SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 06:19 PM
| ||||
| ||||
 Searching/Estracting numerical data KR Feb 6, 1:48 pm show options Newsgroups: microsoft.public.access.forms From: "KR" <kra...@bastyr.edu> - Find messages by this author Date: 6 Feb 2006 13:48:00 -0800 Subject: Extract Number from Fields - SQL Reply | Reply to Author | Forward | Print | Individual Message | Show original | Remove | Report Abuse I am new to the SQL world, and I am trying to come up with a script that will extract only the numerical data from a column of varchar data type . There is not a pattern to the data entered, except that the data that I am looking to extract is a three digit number. If someone could point me in the right direction that would be great. Thanks in advance KR      |
|
02-29-2008, 06:19 PM
| |||
| |||
| Re: Searching/Estracting numerical data KR (kraman@bastyr.edu) writes: > I am new to the SQL world, and I am trying to come up with a script > that will extract only the numerical data from a column of varchar > data type . There is not a pattern to the data entered, except that > the data that > I am looking to extract is a three digit number. If someone could > point me in the right direction that would be great. SELECT cast(col as int) FROM tbl WHERE col LIKE '[0-9][0-9][0-9}' -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
|
02-29-2008, 06:19 PM
| |||
| |||
| Re: Searching/Estracting numerical data On 6 Feb 2006 13:53:35 -0800, KR wrote: > KR > Feb 6, 1:48 pm show options > >Newsgroups: microsoft.public.access.forms >From: "KR" <kra...@bastyr.edu> - Find messages by this author >Date: 6 Feb 2006 13:48:00 -0800 >Subject: Extract Number from Fields - SQL >Reply | Reply to Author | Forward | Print | Individual Message | Show >original | Remove | Report Abuse > >I am new to the SQL world, and I am trying to come up with a script >that will extract only the numerical data from a column of varchar >data type . There is not a pattern to the data entered, except that >the data that >I am looking to extract is a three digit number. If someone could >point me in the right direction that would be great. Hi KR, The function you would use to find where a three digit sequence in varchar data is located is POSINDEX ('%[0-9][0-9][0-9]%', CharData) -- Hugo Kornelis, SQL Server MVP |
|
02-29-2008, 06:19 PM
| |||
| |||
| Re: Searching/Estracting numerical data Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes: > The function you would use to find where a three digit sequence in > varchar data is located is > > POSINDEX ('%[0-9][0-9][0-9]%', CharData) patindex, you mean, n'est-ce pas? -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
|
02-29-2008, 06:19 PM
| |||
| |||
| Re: Searching/Estracting numerical data On Mon, 6 Feb 2006 23:19:18 +0000 (UTC), Erland Sommarskog wrote: >Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes: >> The function you would use to find where a three digit sequence in >> varchar data is located is >> >> POSINDEX ('%[0-9][0-9][0-9]%', CharData) > >patindex, you mean, n'est-ce pas? Ah, oui, c'est vrai!! Thanks for the catch, Erland! -- Hugo Kornelis, SQL Server MVP |
Linear Mode
