getting a specific value from a string

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

Transact-SQL (2000)

getting a specific value from a string

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-11-07 : 11:11:55

Hi,

I have a string as below:

\\twistcat.tm-ltd.co.uk\olasvol001\la_proofer\469415_SISEA_STFFSE.pdf

what i'd like is to extract the '469415' value from the string above. The string may have different values as it is populated via a query. It can be

\\twistcat.tm-ltd.co.uk\olasvol001\la_proofer\456321_VISEA_SFGRFSE.pdf

In this case i require the 456321 value from the string.

Can anyone help me do this. Thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-07 : 11:35:31

[code]
set nocount on
drop table #n
go
create table #n (n int)
declare @i int
set @i =1

while @i < 1000
begin
insert into #n select @i
set @i = @i + 1
end
go

declare @param varchar(100)
set @param = '\\twistcat.tm-ltd.co.uk\olasvol001\la_proofer\456321_VISEA_SFGRFSE.pdf'

declare @file varchar(100)
select @file = substring(substring(@param,max(charindex('\',@param,n))+1,len(@param)),1,charindex('_',substring(@param,max(charindex('\',@param,n))+1,len(@param)))-1) From #n
select @file

[/code]

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-07 : 11:40:34

something like
select substring(@str,len(@str) + 2 - charindex('\',reverse(@str)), charindex('_',right(@str,charindex('\',reverse(@str))-1)-1)

==========================================
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.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-07 : 11:50:24

REVERSE?

Damn, learned something new....again....

Thanks Nigel...

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources