Get given character's position from a string

  • hxd001_810-512717

    SSC Journeyman

    Points: 86

    Comments posted to this topic are about the item Get given character's position from a string

  • John Donaldson

    SSC Journeyman

    Points: 89

    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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply