May 22, 2008 at 11:37 am
Hi All
I got a string which has '_', i need to find the 2nd place of the string, i can use charindex to find the first location, is there any other way to find the second location on SQL 2005
eg : Test_sample_proc
Test_sample
I need a result whic shows
Test_sample
Test_sample
Cheers
May 22, 2008 at 11:47 am
Try this function:-
-- select dbo.f_firstposition('abc,def,ccc,ged','e',1)
-- go
-- Result:4
create function dbo.f_firstposition
(@Str varchar(8000),@StrSep varchar(10),@AppPos int)
returns int
begin
declare @i int
declare @ii int
set @STR=rtrim(ltrim(@Str))--'abc,def,ccc,ged',',',3
set @i=1
select @ii=charindex(@StrSep,@Str)
if @i=@AppPos
return @ii
else
while @AppPos>@i
begin
if charindex(@StrSep,right(@Str,len(@Str)-@ii))<>0
select @ii=charindex(@StrSep,right(@Str,len(@Str)-@ii))+@ii
else
set @ii=0
set @i=@i+1
end
return @ii
end
go
Hope this helps!
Manu
May 22, 2008 at 9:46 pm
It's still CHARINDEX, you just have to be more persistent:
Select CharIndex( @string, '_', CharIndex(@string, '_') + 1)
This returns the second occurrence of "_" in @string.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 23, 2008 at 12:48 am
Hi,
With this querry u can get the second position
DECLARE @String VARCHAR(100)
SET @String='Test_sample_proc'
Select (charindex('_',@String,CharIndex('_',@String,0)+1))
but if u want the result like Test_sample then use this,
DECLARE @String VARCHAR(100)
SET @String='Test_sample_proc'
Select substring(@String,0,(charindex('_',@String,CharIndex('_',@String,0)+1)))
Hope it helps u......
May 23, 2008 at 4:03 am
Thanks a lot guys, this is great,
Cheers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply