Monday, June 29, 2009

Find the first occurance of a string

About FuncFirstOccurrance SQL User defined function

/* This function takes two arguments. The first argument is the string*/
/* that is to be searched and the second argument is the string that */
/* you are looking for the first occurance of. It returns the position*/
/* of that first occurance.*/

CREATE FUNCTION dbo.FuncFirstOccurrance(@String varchar(2000),@Occurance as varChar(2000)) RETURNS integer


Declare @i as Integer
Declare @Result as Integer
Declare @Pos as Integer

Set @i=1
Set @Result = 9999

while(@i<=len(@String)) Begin set @Pos = charindex(substring(@String,@i,1),@Occurance) if @Pos >0 Begin
if @i < @Result begin Set @Result = @i End End Set @i = @i + 1 End Return (@Result) End USAGE

dbo.FuncFirstOccurance('Your String to Search','Your String to be searched')


dbo.FuncFirstOccurance('HelloEveryone','o') will return 5

Please rate my code if you find this helpful

No comments:

Post a Comment