find numeric in string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

find numeric in string

Author

Topic

luzippu
Starting Member

23 Posts

Posted - 2012-11-23 : 11:30:53

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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-23 : 11:48:09

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

luzippu
Starting Member

23 Posts

Posted - 2012-11-23 : 12:10:09

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

skc40
Starting Member

34 Posts

Posted - 2012-11-23 : 15:30:36

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

Elizabeth B. Darcy
Starting Member

39 Posts

Posted - 2012-11-23 : 18:38:37

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.

luzippu
Starting Member

23 Posts

Posted - 2012-12-05 : 08:39:34

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-05 : 08:53:02

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

luzippu
Starting Member

23 Posts

Posted - 2012-12-05 : 09:24:01

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources