Get given character's position from a string

• hxd001_810-512717

  SSC Journeyman

  Points: 86

  More actions

  December 19, 2007 at 12:13 am

  #377565

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

• John Donaldson

  SSC Journeyman

  Points: 89

  More actions

  February 14, 2008 at 1:33 pm

  #778573

  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