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.

 

9 comments

  1. Amazing blog! Do you have any helpful hints for aspiring writers?
    I’m hoping to start my own website soon but I’m a little lost on everything.
    Would you advise starting with a free platform like WordPress or go for a paid option? There are so many choices out there
    that I’m completely confused .. Any suggestions? Many thanks!

    Like

  2. Everything is very open with a very clear explanation of the issues.
    It was really informative. Your website is very helpful.
    Many thanks for sharing!

    Like

  3. Wow that was strange. I just wrote an extremely long comment
    but after I clicked submit my comment didn’t show up. Grrrr…
    well I’m not writing all that over again. Anyhow, just wanted to say excellent blog!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s