SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 find numeric in string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

luzippu
Starting Member

United Kingdom
23 Posts

Posted - 11/23/2012 :  11:30:53  Show Profile  Reply with Quote
hi again,

i have a column with numeric and alphanumeric records, e.g.:

'abc 123 xyz 1234567 test'
'1111111 bla bla bla 761119001111'
'98765432'
'a555b 987,611 bla bla bla 1223344'
'9999'

I need to pull out of this fields only consecutive numeric digits where it begins with '1' and it is 7 digits long.

for example from the rows above the results should be (in order):
'1234567'
'1111111'
NULL
'1223344'
NULL

any clever idea?

many thanks
paul

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/23/2012 :  11:48:09  Show Profile  Reply with Quote
SELECT SUBSTRING(yourColumn,PATINDEX('%[1][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%',yourColumn),7).
FROM yourTable

this is untested as I don't have access to SQL Server.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

luzippu
Starting Member

United Kingdom
23 Posts

Posted - 11/23/2012 :  12:10:09  Show Profile  Reply with Quote
hi jim, testing your solution it looks like it's bringing back the first 6 characters on every row... numeric or non numeric..

thanks
paul
Go to Top of Page

skc40
Starting Member

29 Posts

Posted - 11/23/2012 :  15:30:36  Show Profile  Reply with Quote
Hi Jimf--I don't know if it's the right solution, but got close to what you wanted.

declare @table table (Col1 Varchar(100))
Insert into @table values ('abc 123 xyz 1234567 test')
Insert into @table values ('1111111 bla bla bla 761119001111')
Insert into @table values ('98765432')
Insert into @table values ('a555b 987,611 bla bla bla 1223344')
Insert into @table values ('9999')

select SUBSTRING(Col1,PATINDEX('%[1][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%',Col1),7) from @Table
Declare @char int
Set @Char=0
While @Char<26
Begin
Update @Table Set Col1=Replace(Replace(Replace(Col1, Char(65+@Char),''),' ',''),',','')
Set @Char=@Char+1
End

Select Left(Col1,7),*,SUBSTRING(Col1,PATINDEX('%[1^][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',Col1),7) from @Table
Go to Top of Page

Elizabeth B. Darcy
Starting Member

United Kingdom
39 Posts

Posted - 11/23/2012 :  18:38:37  Show Profile  Reply with Quote
I had to make a change to the code JimF posted, but with that change, it is doing what it should be doing:
declare @table table (Col1 Varchar(100))
Insert into @table values ('abc 123 xyz 1234567 test')
Insert into @table values ('1111111 bla bla bla 761119001111')
Insert into @table values ('98765432')
Insert into @table values ('a555b 987,611 bla bla bla 1223344')
Insert into @table values ('9999')

SELECT 
	SUBSTRING(Col1,NULLIF(PATINDEX('%[1][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%',Col1),0),7)
FROM @table



________________________________________
-- Yes, I am indeed a fictional character.
Go to Top of Page

luzippu
Starting Member

United Kingdom
23 Posts

Posted - 12/05/2012 :  08:39:34  Show Profile  Reply with Quote
quote:
Originally posted by Elizabeth B. Darcy
'a555b 987,611 bla bla bla 1223344'


Thanks Liz, it all seems to work ok except for where the 7 digits string is at the end of the field.. the example above should bring back '1223344'

If it can't be done, thanks anyway... it covers for most of the scenarios.

paul
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/05/2012 :  08:53:02  Show Profile  Reply with Quote
let's try this

SELECT
CASE WHEN PATINDEX('%[1][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%',Col1) > 0
THEN SUBSTRING(Col1,NULLIF(PATINDEX('%[1][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%',Col1),0),7)
WHEN PATINDEX('%[^0-9][1][0-9][0-9][0-9][0-9][0-9][0-9]',Col1) > 0
THEN RIGHT(Col1,7)
END
FROM @table


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

luzippu
Starting Member

United Kingdom
23 Posts

Posted - 12/05/2012 :  09:24:01  Show Profile  Reply with Quote
Thanks Jim, that sorted it!!

i'm only left with a couple of scenarios where it doesn't quite bring the right result:
'abc 31076586a' brings back '1076586' when i'm expecting a NULL

but i can leave with these as there's only a few re-occurrences.

Many thanks!
paul
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000