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 searched for within the String1. ‘start_position’ and ‘nth_appearance’ are optional. Through ‘start_position’ you can specify from which point in ‘string1’ the search should start and ‘nth_appearance’ is to specify the number of occurrences. By default ‘start_position’ and ‘nth_appearance’ are 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 occurrence 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 the 11th position in string1.

Now, let us see in 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 don’t 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 behavior of ‘INSTR’ function in SQL Server, then there is no other way except to create a user-defined function.