Need a SQL Server command for instr

Hi,

first of all, you are wrong with your example:

if you want the location of the third 'i' in Mississippi, the right command is instr('mississippi','i',1,3) and not instr('mississippi',i,3,1).

Here is the format of INSTR:

INSTR(str,set[,start[,n]])

str is the string to search.

set is the substring to search for in str.

start is the position in str where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1.

If start is negative, the function counts back start number of characters from the end of str and then searches towards the beginning of str.

n is the nth appearance of set. This is optional. If omitted, it defaults to 1.

So far for explanation!!!

Here is my answer:

MS SQL Server has no function INSTR like it is implemented in ORACLE.

But you can do 2 things with SQL Server:

1.)
Try to use the SQL Server functions CHARINDEX or PATINDEX to search your strings.
Format: CHARINDEX(str,set, [,start])
It nearly offers the same functionality as INSTR, but misses the optional parameter n for the nth appearance of a substring.
CHARINDEX always returns the first position of set in str, beginning from position start.

Format: PATINDEX(pattern,str)
Allows to search for a string pattern in str with support of wildcards in the search pattern string.

See following link for a good explanation of both functions: http://www.databasejournal.com/features/mssql/article.php/3071531

2.)
If CHARINDEX OR PATINDEX do not meet your requirements you must define your own INSTR-function in SQL Server with Transact-SQL routine CREATE FUNCTION.
See the following link if you are not familiar with user-defined functions: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp

Please contact me, if you need the SQL command for the user-defined function INSTR equivalent to Oracle INSTR!!!

GOOD LUCK!!!
Tommi
:o)))

Hi,

Here is the complete SQL command for the user-defined INSTR-function equivalent to Oracle INSTR:

CREATE FUNCTION INSTR (
@AStr VARCHAR(4000),
@ASet VARCHAR(255),
@Start INT,
@n INT )
RETURNS INT
AS
BEGIN
DECLARE @tmp VARCHAR(4000),
@StrLength INT,
@ReturnValue INT,
@i INT,
@NextStart INT
SET @tmp = SUBSTRING(@AStr,1,LEN(@AStr) + @Start)
SET @StrLength = LEN(@tmp)
IF @n > 0 BEGIN
IF @StrLength <= @Start OR @StrLength <= @n or @Start = 0
SET @ReturnValue = 0
ELSE BEGIN
IF @Start < 0 BEGIN
SET @n = LEN(REPLACE(@tmp,@ASet,@ASet+';')) - LEN(@tmp) - @n +1
IF @n < 0 or @n = 0
RETURN 0
END
ELSE BEGIN
SET @tmp = SUBSTRING(@AStr,@Start,LEN(@AStr))
IF @n > LEN(REPLACE(@tmp,@ASet,@ASet+';')) - LEN(@tmp)
RETURN 0
END

SET @i = 0
IF LEN(@tmp) > 0 BEGIN
IF @Start > 1
SET @ReturnValue = @Start -1
ELSE
SET @ReturnValue = 1
WHILE @c < @n BEGIN
SET @ReturnValue = CHARINDEX(@ASet,@tmp,ISNULL(@NextStart,1))
IF @ReturnValue > 0
SET @NextStart = @ReturnValue +1
ELSE
SET @NextStart = @ReturnValue
SET @i = @i+1
END
END
END
END
ELSE
RETURN 0
IF @Start > 0
SET @ReturnValue = @ReturnValue + @Start-1
ELSE
IF @Start < 0
SET @ReturnValue = @ReturnValue
RETURN @ReturnValue
END