SQL INSTR Functions Explained with Examples

In this article, we’ll look at the INSTR function (including its variants), explain how to use it, and see some examples.

Purpose of the SQL INSTR Function

The SQL INSTR function allows you to search a string, for the occurrence of another string, and return the position of the occurrence within the string.

It’s good for finding if a string exists in another string, and also for performing additional tasks on, like other text manipulation functions or updating data.

Both the string and substring parameters can be any of the following data types:

• CHAR
• VARCHAR2
• NCHAR
• NVARCHAR2
• CLOB
• NCLOB

In Oracle, there are several variations. These INSTR functions are variations of the basic INSTR function, which searches inside a string for a substring, and returns the position in the string of the first occurrence of the substring.

Where these variations differ is how they determine the position of the substring to return:

• INSTRB calculates lengths using bytes
• INSTRC calculates lengths using Unicode characters
• INSTR2 calculates lengths using USC2 code points
• INSTR4 calculates lengths using USC4 code points

This INSTR function only exists in Oracle and MySQL:

• SQL Server has CHARINDEX
• Postgres has POSITION

 

SQL INSTR Function Syntax and Parameters

The syntax for the INSTR function is:

INSTR( string, substring, [start_position], [occurrence] )

This Oracle INSTR function is actually different from other functions in that it has several different versions. Each of them varies in how they determine the position of the substring to return:

• INSTR calculates the length using characters defined by the input character set.
• INSTRB calculates the length using bytes.
• INSTRC calculates the length using Unicode complete characters.
• INSTR2 calculates the lengths using USC2 code points.
• INSTR4 calculates the lengths using USC4 code points.

The parameters of the INSTR function and its variants are:

• string (mandatory): This is the text string that is searched in. It is usually the larger of the two strings. If you are looking to “find X within Y”, this is the Y component.
• substring (mandatory): This is the text string that is searched for. It is usually the smaller of the two strings. If you are looking to “find X within Y”, this is the X component.
• start_position (optional): This is a nonzero integer, and indicates where in the string value to start the search. The default is 1, which is the start of the string. If it is negative, the function searches and counts from the end of string and goes backwards.
• occurrence (optional): This is a positive integer, and indicates which occurrence of the substring the function should search for. The default value is 1, which means the functions searches for the first occurrence.

The search performed in this function is case-sensitive.

 

SQL INSTR Return Value

The function returns a NUMBER value, which is the number in the location of the string where the substring is found. The numbering starts at 1, so if it is the first character, the function will return 1.

If the substring is not found, the function will return a zero (0).

 

Examples of the INSTR Functions

Here are some examples of the SQL INSTR function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This example just searches for the first occurrence of the letter “e”.

SELECT INSTR('Melbourne, Australia', 'e');

Result:

2

 

Example 2

This example searches for the first occurrence of the letter “m” (note that it is in lower case).

SELECT INSTR('Melbourne, Australia', 'm');

Result:

0

 

Example 3

This example searches for an uppercase “M”.

SELECT INSTR('Melbourne, Australia', 'M');

Result:

1

 

Example 4

This example searches for the letter “e”, starting from position 1 and finding the second occurrence.

SELECT INSTR('Melbourne, Australia', 'e', 1, 2);

Result:

9

This is the “e” at the end of the word “Melbourne”.

 

Example 5

This example searches for the letter “e”, starting from position 5, and finds the first occurrence.

SELECT INSTR('Melbourne, Australia', 'e', 5, 1);

Result:

9

This is the “e” at the end of the word “Melbourne”.

 

Example 6

This example searches for the letter “e”, starting from position 5, and finds the second occurrence.

SELECT INSTR('Melbourne, Australia', 'e', 5, 2)
FROM DUAL;

Result:

0

No second occurrence of “e” is found after this point.

 

Example 7

This example searches for the string “bou” within the entire string.

SELECT INSTR('Melbourne, Australia', 'bou');

Result:

4

 

Example 8

This example searches for the letter “a” but starts from the end of the string.

SELECT INSTR('Melbourne, Australia', 'a', -1);

Result:

20

The first “a” that is found is the value at the end of “Australia”.

 

Example 9

This example searches for the letter “a”, but starts from the end of the string, and finds the second occurrence.

SELECT INSTR('Melbourne, Australia', 'a', -1, 2);

Result:

17

This is the “a” that is found in the middle of the word “Australia”, as it is the second occurrence working backward from the string.

 

Example 10 – INSTR Variants (Oracle)

Here are some examples of the INSTR variation functions.

This example shows how each of the INSTR functions treats a specific string. I’ve used the TO_MULTI_BYTE function to show differences with some of the INSTR functions.

SELECT
TO_MULTI_BYTE('Database Star table column row') AS sample,
INSTRB(TO_MULTI_BYTE('Database Star table column row'), TO_MULTI_BYTE('s'), 1) AS instrb_test,
INSTRC(TO_MULTI_BYTE('Database Star table column row'), TO_MULTI_BYTE('s'), 1) AS instrc_test,
INSTR2(TO_MULTI_BYTE('Database Star table column row'), TO_MULTI_BYTE('s'), 1) AS instr2_test,
INSTR4(TO_MULTI_BYTE('Database Star table column row'), TO_MULTI_BYTE('s'), 1) AS instr4_test
FROM dual;

Result:

SAMPLE	INSTRB_TEST	INSTRC_TEST	INSTR2_TEST	INSTR4_TEST
Ｄａｔａｂａｓｅ　Ｓｔａｒ　ｔａｂｌｅ　ｃｏｌｕｍｎ　ｒｏｗ	19	7	7	7

You can see that some of the values are different.

Similar Functions

Some functions which are similar to the INSTR function are:

• REGEXP_INSTR – Similar to the INSTR function, but allows for regular expressions.
• LENGTH – Returns the length of the provided string.
• REPLACE – Replaces one string with another string in the provided value.
• SUBSTR – Returns a part of the provided string, based on the specified position and length.

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.