InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 11:00:47 GMT20http://www.sqlservercentral.com/Forums/Topic434636-1159-1.aspxFunction has a bug in it. If the @AppPos is two higher than the last match, the result is incorrect.Exampleselect dbo.f_firstposition('abc,def',',',3)goResult: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 intbegin 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&gt;@i begin if charindex(@StrSep,right(@Str,len(@Str)-@ii))&lt;&gt;0 select @ii=charindex(@StrSep,right(@Str,len(@Str)-@ii))+@ii else begin set @ii=0 break end set @i=@i+1 end return @iiendgoThu, 14 Feb 2008 13:33:09 GMTJohn Donaldsonhttp://www.sqlservercentral.com/Forums/Topic434636-1159-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/User+Defined+Function/61768/">Get given character's position from a string</A>[/B]Wed, 19 Dec 2007 00:13:58 GMThxd001_810-512717