| Author |
 Topic  |
|
|
luzippu
Starting Member
United Kingdom
21 Posts |
 Posted - 11/23/2012 : 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
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 11/23/2012 : 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
United Kingdom
21 Posts |
Posted - 11/23/2012 : 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
12 Posts |
Posted - 11/23/2012 : 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
United Kingdom
39 Posts |
Posted - 11/23/2012 : 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
United Kingdom
21 Posts |
Posted - 12/05/2012 : 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
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 12/05/2012 : 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
United Kingdom
21 Posts |
Posted - 12/05/2012 : 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 |
|
|
| |
Topic |
|
find numeric in string
