extract part of 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 2000 Forums

SQL Server Development (2000)

extract part of string

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2010-10-29 : 14:30:19

Hi,

I want to extract part of string.

create table #temp1 (description varchar(50))

insert into #temp1 values ('ashdjdkd_2345_9087')
insert into #temp1 values ('ashdjdkd_4567_9012')
insert into #temp1 values ('ash_djdkd_1234_2387')
insert into #temp1 values ('ashdj_dkd_7890_6687')

want output as:
2345
4567
1234
7890

the code has to be written in a way where it takes care of 3rd and 4th row.

nr
SQLTeam MVY

12543 Posts

Posted - 2010-10-29 : 16:10:28

select substring(description,strtloc,endloc-strtloc)
from
(
select description, strtloc = charindex('_',description)+1, endloc = len(description) - charindex('_',reverse(description))
from #temp1
) a

Probably need to add or subtract 1 from locations but you'll get the idea.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sachingovekar
Posting Yak Master

101 Posts

Posted - 2010-10-30 : 10:49:59

doesn't work properly... Thanks for trying

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-30 : 11:04:59

sorry - didn't notice extra _
Note - this allows for variable length numbers - if they are always 4 digits it could be a lot simpler.

select description, substring(description, ln-strtloc+2, strtloc-endloc-1), SUBSTRING(description,ln-endloc+2,endloc-1)
from
(
select description, endloc, strtloc = charindex('_',reverse(description), endloc+1), ln = LEN(description)
from
(
select description, endloc = charindex('_',reverse(description))
from #temp1
) a
) b

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-01 : 07:02:47

If the pattern is same and dot in the data

select parsename(replace(description ,'_','.'),2) from #temp1

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-01 : 07:19:17

quote:
Originally posted by nigelrivett

sorry - didn't notice extra _
Note - this allows for variable length numbers - if they are always 4 digits it could be a lot simpler.

select description, substring(description, ln-strtloc+2, strtloc-endloc-1), SUBSTRING(description,ln-endloc+2,endloc-1)
from
(
select description, endloc, strtloc = charindex('_',reverse(description), endloc+1), ln = LEN(description)
from
(
select description, endloc = charindex('_',reverse(description))
from #temp1
) a
) b

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Nigel, what happened to your old id?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources