Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get given character's position from a string Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2007 12:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 03, 2008 8:47 PM
Points: 8, Visits: 18
Comments posted to this topic are about the item Get given character's position from a string
Post #434636
Posted Thursday, February 14, 2008 1:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:01 AM
Points: 2, Visits: 136
Function has a bug in it. If the @AppPos is two higher than the last match, the result is incorrect.

Example

select dbo.f_firstposition('abc,def',',',3)
go
Result:4 Incorrect

One Solution - Break when you can't find the next occurrence :

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))
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
begin
set @ii=0
break
end
set @i=@i+1
end

return @ii
end
go
Post #455975
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse