How to extract certain format piece of string ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

How to extract certain format piece of string ?

Author

Topic

nmaruti
Starting Member

10 Posts

Posted - 2009-04-21 : 20:27:59

Hi ,

I have a column whose values are like below :

FILE NO. 66-41990575
SLXEEC. ED., FILE: 36-34490384 , DLTD.
SLXEEC. EDD., ETNRLI: 44-34490384 , COMPNAY.

I want the output of this column as :
66-41990575
36-34490384
44-34490384

The appearence of these values may fall at any place of the string. So we cannot write SUBSTRING function to extract. How can i detect
certain format (ex: XX-XXXXXXX , where X=Numbers) of Number string in alphanumric string ex: SLXEEC. ED., FILE: 36-34490384 , DLTD.

Its Urgent.Please help.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-21 : 20:33:21

If number format will be same:

Select substring(column,patindex('[0-9]%',column)+1,11)

Not tested.

kumar1248
Starting Member

20 Posts

Posted - 2009-04-21 : 22:56:13

Thanks sodeep. But forgot to mention , the column contains other digit numbers too like:

FILE NO. 66-41990575 ,STREET 345
SLXEEC. ED., FILE: 36-34490384 , DLTD. ,APT 45934
24 SOUTH , SLXEEC. EDD., ETNRLI: 44-34490384 , COMPNAY.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-22 : 00:30:32

declare @tab table(val varchar(64))
insert into @tab select
'FILE NO. 66-41990575 ,STREET 345' union all select
'SLXEEC. ED., FILE: 36-34490384 , DLTD. ,APT 45934' union all select
'24 SOUTH , SLXEEC. EDD., ETNRLI: 44-34490384 , COMPNAY'

Select patindex('%[0-9]-%',val),
substring(val,patindex('%[0-9]-%',val)-1,11) from @tab

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-22 : 02:39:05

Hi,

declare @table1 table (id varchar(100))
insert into @table1 values ('FILE NO. 66-41990575')
insert into @table1 values ('SLXEEC. ED., FILE: 36-34490384 , DLTD.')
insert into @table1 values ('SLXEEC. EDD., ETNRLI: 44-34490384 , COMPNAY.')

select charindex('-',id),substring(id,charindex('-',id)-2,11) from @table1

Kunal

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-22 : 02:46:08

quote:
Originally posted by kunal.mehta

Hi,

declare @table1 table (id varchar(100))
insert into @table1 values ('FILE NO. 66-41990575')
insert into @table1 values ('SLXEEC. ED., FILE: 36-34490384 , DLTD.')
insert into @table1 values ('SLXEEC. EDD., ETNRLI: 44-34490384 , COMPNAY.')

select charindex('-',id),substring(id,charindex('-',id)-2,11) from @table1

Kunal

Better one is bklr's solution...
bcos it has additional starting number check, while the later solution has only a '-' check..

thanx...

nmaruti
Starting Member

10 Posts

Posted - 2009-04-22 : 13:58:34

Thanks Bklr. Your solution is working great. But had a problem. If there is no digits with XX-XXXXXXX format then it is returning some of the text.

How can we solve this?Please udpate the qry.

Thanks,
Nmaruti

notmyrealname

98 Posts

Posted - 2009-04-22 : 15:46:13

Hi nmaruti.

This should work for you.

SELECT SUBSTRING(colName, PATINDEX('%[0-9]-[0-9]%', colName) - 2, 12) AS ID
FROM tblName
WHERE (PATINDEX('%[0-9]-[0-9]%', colName) > 0)

Hope it helps.

nmaruti
Starting Member

10 Posts

Posted - 2009-04-22 : 17:08:55

Hi jswota,
thanks for your reply. But again its failing at following data:

9404-08 WEST 47TH STREET
9404-08 WEST 47TH STREET

Where it is returning :

404-08 WEST
404-08 WEST

How to overcome this? I want Just XX-XXXXXXXX format peice of data where X = Numbers.

Scratching my head since 2 days.
Please help.

Thanks,
Nmaruti

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-04-22 : 17:33:31

It's ugly but works


declare @table1 table (id varchar(100))
insert into @table1 values ('FILE NO. 66-41990575')
insert into @table1 values ('SLXEEC. ED., FILE: 36-34490384 , DLTD.')
insert into @table1 values ('SLXEEC. EDD., ETNRLI: 44-34490384 , COMPNAY.')

select left(SUBSTRING(id, PATINDEX('% [0-9][0-9]-[0-9]%', id)  ,len(id) ),charindex(' ',SUBSTRING(id, PATINDEX('%[0-9]%', id) ,len(id) )+ ' ')) AS ID from @table1
where  (PATINDEX('% [0-9][0-9]-[0-9]%', id) > 0)

An infinite universe is the ultimate cartesian product.

nmaruti
Starting Member

10 Posts

Posted - 2009-04-22 : 18:12:33

Hi cat_jesus,

Its failed at
insert into @table1 values ('DISTRICT #46 FILE: 36-34490334')
The output is coming as 36

notmyrealname

98 Posts

Posted - 2009-04-22 : 19:06:21

quote:
Originally posted by nmaruti

Hi jswota,
thanks for your reply. But again its failing at following data:

9404-08 WEST 47TH STREET
9404-08 WEST 47TH STREET

Where it is returning :

404-08 WEST
404-08 WEST

How to overcome this? I want Just XX-XXXXXXXX format peice of data where X = Numbers.

Scratching my head since 2 days.
Please help.

Thanks,
Nmaruti

Sorry about that. I didn't realize that you could have a similar format elsewhere.

What if you use the entire code format? If the code is in fact always in the format of ##-########.

SELECT SUBSTRING(colName, PATINDEX('%[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', colName) - 1, 12) AS ID
FROM tblName
WHERE (PATINDEX('%[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', colName) > 0)

Will that work? It should work whenever it find the exact code format.

Hope this helps.

matty
Posting Yak Master

161 Posts

Posted - 2009-04-23 : 01:09:10

declare @tab table(val varchar(64))
insert into @tab
select 'FILE NO. 66-41990575 ,STREET 345' union all
select 'SLXEEC. ED., FILE: 36-34490384 , DLTD. ,APT 45934' union all
select '24 SOUTH , SLXEEC. EDD., ETNRLI: , COMPNAY' union all
select 'DISTRICT #46 FILE: 77-34490334' union all
select '9404-08 WEST 47TH ST'

SELECT val
FROM
(
SELECT PATINDEX('%[0-9]-%',val) AS pos,
SUBSTRING(val,PATINDEX('%[0-9]-%',val)-1,11) AS val
FROM @tab
)t
WHERE pos > 0 AND ISNUMERIC(SUBSTRING(val,4,LEN(val)))= 1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-23 : 06:18:30

try this too maruthi,


declare @tab table(val varchar(64))
insert into @tab select
'FILE NO. 66-41990575 ,STREET 345' union all select
'SLXEEC. ED., FILE: 36-34490384 , DLTD. ,APT 45934' union all select
'24 SOUTH , SLXEEC. EDD., ETNRLI: 44-34490384 , COMPNAY'union all
select '24 SOUTH , SLXEEC. EDD., ETNRLI: , COMPNAY' union all
select 'DISTRICT #46 FILE: 77-34490334' union all
select '9404-08 WEST 47TH ST'

Select patindex('%[0-9][0-9]-%',val),
substring(val,patindex('%[0-9][0-9]-%',val)-1,11) from @tab
where patindex('%[0-9][0-9]-%',val) > 0 and substring(val,patindex('%[0-9][0-9]-%',val)-1,11) not like '%[a-z]%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources