INSTR Function

INSTR searches string for substring.

--> position is an integer indicating the character of string where SQL Server begins the search.

-->If position is negative, SQL Server counts and searches backward from the end of string.

occurrence is an integer indicating which occurrence of string SQL Server should search for.

The value of occurrence must be positive. The function returns an integer indicating the position of the

character in string that is the first character of this occurrence. The default values of both position and

occurrence are 1, meaning SQL Server begins searching at the first character of string for the first occurrence

of substring. The return value is relative to the beginning of string, regardless of the value of position,

and is expressed in characters. If the search is unsuccessful (if substring does not appear occurrence times

after the position character of string) the return value is 0.

CREATE FUNCTION INSTR (
@inString VARCHAR(4000), 
@SearchStr VARCHAR(255),
@Position INT,
@Occurrence INT )
RETURNS INT
AS
BEGIN
	DECLARE @retVal INT,@tempStr VARCHAR(4000),	
		 @strLen INT, @i INT, @tVal INT
	SET @tempStr = sisi.SUBSTR(@inString,1,LEN(@inString) + @position)
	SET @strLen = LEN(@tempStr)
	IF @Occurrence <= 0
		RETURN 0
	ELSE
	BEGIN
		IF @strLen <= @Position OR @strLen <= @Occurrence or @position = 0
			SET @retVal = 0
		ELSE
		BEGIN
			IF @position < 0 
			BEGIN
				SET @Occurrence = LEN(REPLACE(@tempStr,@searchStr,@searchStr+';')) - LEN(@tempStr) - @Occurrence +1
				IF @occurrence < 0 or @occurrence = 0
					RETURN 0
				ELSE IF @occurrence = 0
					SET @occurrence = @occurrence
			END
			ELSE
			BEGIN
				SET @tempStr = sisi.SUBSTR(@inString,@Position,LEN(@inString))
				IF @Occurrence > LEN(REPLACE(@tempStr,@searchStr,@searchStr+';')) - LEN(@tempStr) 
				 	RETURN 0
			END
			
			SET @i = 0

			IF LEN(@tempStr) > 0 
			BEGIN
				IF @position > 1
					SET @retVal = @position -1
				ELSE
					SET @retVal = 1			
				WHILE @i < @Occurrence
				BEGIN
					SET @retVal = CHARINDEX(@SearchStr,@tempStr,ISNULL(@tVal,1)) 
					IF @retVal > 0
						SET @tVal = @retVal +1
					ELSE
						SET @tVal = @retVal
					SET @i = @i+1
				END
			END
		END
	END
	IF @position > 0
		SET @retVal = @retVal + @position-1
	ELSE IF @position < 0
		SET @retVal = @retVal 
	RETURN @retVal
END

Rate

3 (2)

Share

Share

Rate

3 (2)