SQL Server - CHARINDEX - Oracle INSTR

There is a ‘CharIndex’ function in SQL Server which is similar to the Oracle ‘Instr’ function.

In Oracle, the syntax of the INSTR function is :

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

String1 in the above syntax represents the string that you need to search in, whereas String2 is the substring that needs to be search for within the String1.

start_position and nth_appearance are optionals. Through start_position you can specify from which point in string1 the search should start and nth_appearance is to specify the number of occurance. By default start_position and nth_appearance is set to 1.

Now, let's check with one example.

SELECT INSTR('Rhythm of the band', 'th') FROM Dual;
SELECT INSTR('Rhythm of the band', 'th', 1, 1) FROM Dual;

The above select statements would return 4 as your output since 'th' was found to be positioned at the 4th character. But 'th' has been spelled twice in "Rhythm of the band" and if you require a second occurance from the string, then you need to change the statement like this_

SELECT INSTR('Rhythm of the band', 'th', 1, 2) FROM Dual;

The above statement however would return 11 as your output since 'th' was found at 11th position in string1.

Now, let's work on SQL Server for the same.

CHARINDEX is the alternative in SQL Server for INSTR function but it is not exactly its equivalent.

CHARINDEX ( expression1 , expression2 [ , start_location ] )

The above syntax is the same as with INSTR function except for nth_appearance. We donot have an option to set the nth_appearance.

SELECT CHARINDEX('th','Rhythm of the band')

The above statement would return 4 as your output since 'th' was found at the 4th character position.

SELECT CHARINDEX('th','Rhythm of the band', 5)
SELECT CHARINDEX('th','Rhythm of the band', 6)
SELECT CHARINDEX('th','Rhythm of the band', 7)
SELECT CHARINDEX('th','Rhythm of the band', 8)

The above statements would return 11 as your output since 'th' was found at the 11th position after the first 5 characters.

If you require an exact behaviour of INSTR function in SQL Server, then follow the link to create a user defined function.

http://www.dbforums.com/microsoft-sql-server/1101462-equivalent-oracles-instr-4-parameters-sql-server.html