Find the first occurance of a string

Posted by VIJI Monday, June 29, 2009

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
AS

BEGIN

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')

Example:

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


Please rate my code if you find this helpful

0 Responses to Find the first occurance of a string

Post a Comment