Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Posts
    3

    Unanswered: Equivalent of Oracle's INSTR( with 4 parameters) in SQL Server

    The syntax for Oracle's INSTR function is

    instr (string1, string2, [start_position], [nth_appearance])

    string1 is the string to search.

    string2 is the substring to search for in string1.

    start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.

    nth_appearance is the nth appearance of string2. This is optional. If omiited, it defaults to 1.

    In SQL Server, we are having CHARINDEX and PATINDEX functions. But they will not accept the fourth paremeter (nth_appearance)

    Do anybody know the solution for this ????

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    CREATE FUNCTION udf_Instr
    	(@str1 varchar(8000), @str2 varchar(1000), @start int, @Occurs int)
    RETURNS int
    AS
    BEGIN
    	DECLARE @Found int, @LastPosition int
    	SET @Found = 0
    	SET @LastPosition = @start - 1
    
    	WHILE (@Found < @Occurs)
    	BEGIN
    		IF (CHARINDEX(@str1, @str2, @LastPosition + 1) = 0)
    			BREAK
    		  ELSE
    			BEGIN
    				SET @LastPosition = CHARINDEX(@str1, @str2, @LastPosition + 1)
    				SET @Found = @Found + 1
    			END
    	END
    
    	RETURN @LastPosition
    END
    GO
    
    SELECT dbo.udf_Instr('x','axbxcxdx',1,4)
    GO
    
    
    DROP FUNCTION udf_Instr
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •