INSTR in SQL: A Guide with Detailed Explanation

Introduction

The INSTR function looks for substrings in a string according to INSTR in SQL. This function would return an integer that shows the location of the first character of this occurrence in the string. The INSTR function generates a string from characters in the input character set. Instead of the characters, the INSTR in SQL uses the bytes.

INSTRC would make use of full Unicode characters. Moreover, UCS2 code points are used in an INSTR2 and INSTR4 to make use of the UCS4 code pointing system.

Now, let’s first understand the purpose of SQL INSTR function.

INSERT INTO in_data VALUES ('welcome to sql', 'sql');
INSERT INTO in_data VALUES ('programming in c', 'cpp');
INSERT INTO in_data VALUES ('java programming', 'java');

SELECT * FROM in_data;

What is the purpose of SQL INSTR Function

The INSTR function of SQL enables you to search a string for the occurrence of any other string by returning the position of the occurrence within a string.

This function is good if you want to find a string that exists in another string. Moreover, it also performs additional tasks like other text manipulation functions or updating the data.

Both a string and a substring parameter would be of the below-mentioned types.

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

In Oracle, there are a number of variations available. These INSTR functions are variations of the basic INSTR function that searches inside a string for a substring, & then returns the position in any string of the first occurrence of the substring.

Moreover, these variations differ in the way they position a substring to return:

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

This INSTR function only exists in Oracle and MySQL:

• The SQL server has CHARINDEX
• Postgres has POSITION

Now, let’s understand how does an INSTR in SQL

How does an INSTR in SQL

An SQL, being a query language, would contain various in-built functionality to deal with the String data values of any database

The INSTR function in SQL () is the most interesting function.

SQL INSTR () function identifies the first occurrence of a string or a character in the other strings. Thus, this function witnesses the position of the first occurrence of any string or a substring in another string data value.

Having understood how the INSTR in SQL () functions, let us now understand its structure and syntax.

Understanding the Syntax of INSTR

A INSTR function in SQL () accepts two different parameters:

• A string/character to search for in the other strings' data value.
• A string around within which the occurrence of the character/string would be found.

INSTR(string1, string2);

The INSTR in SQL function outputs an integer value stating the index of the first occurrence of the string that needs to be searched.

Now, let’s understand the parameters of the INSTR function and its variant are:

• String (this is mandatory): This is a text string that we use for searching. Moreover, it is usually the largest of two strings. For instance, if you are looking to search “X” within “Y,” this one is the “Y” component.
  
• Substring (this is mandatory): It is a text string that is searched for, but it is usually the smaller of the two strings. If you are “searching for X within Y” this is the “X” component.
  
• The start_position (optional): It is a nonzero integer indicating where in the string value to start the search. The default is 1; that is the start of the strong. But if it is negative, the string will go backward.
  
• Occurrence (optional): This one is the positive integer and indicates which occurrence of the substring the function should search for. The default value here is 1, which means the function searches for the first occurrence.

The search performed in this function is case-sensitive.

INSTR in SQL and Return

This function outputs the NUMBER value, that is, the number in the location of the string where the substring is found. Here, the numbering starts from 1, so if it is the first character, the function will return the value as “1”But, if the substring is not found, the function will return the output value as a zero ()

In the next section, we will examine the implementation of the INSTR in SQL function through various examples.

Examples of INSTR in a SQL ()

Below are examples of the SQL INSTR function. These examples will prove to be the best way to learn about code. Moreover, an explanation of each of them is given for a better understanding.

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 shows the search for the very first occurrence of the letter “e”

SELECT INSTR (‘Melbourne, Australia’ , ‘e’)

RESULT = 2

Example 2

This example highlights the search for the very first occurrence of the letter “m” (note that it is in lowercase)

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

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

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

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’ , ‘-1’)

RESULT = 20

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’ , -1, 2)

RESULT = 17

Now, let’s under some of the similar functions like INSTR In SQL

Some similar functions

A few of the functions that are similar to INSTR in SQL function are:

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

Now, let’s quickly wrap up the article.

If you wish to learn more about by finding INSTR in SQL Oracle

Wrapping Up!

In conclusion, an INSTR in SQL function is invaluable for searching and locating the substrings within the strings, thus facilitating efficient data manipulation and retrieval. With its capability to pinpoint the position of occurrences and their variations by catering to different data types and search criteria, an INSTR enhances the querying ability of the Oracle and MySQL databases.

A series of illustrative examples mentioned in this article help you gain a complete understanding of INSTR and SQL’s functionality. Further, it also acknowledges similar functions like REGEXP_INSTR and LENGTH to recognize the broader spectrum of text manipulation tools available in SQL.

By enabling INSTR in SQL, professionals can empower databases to streamline operations and enhance data analysis.

Frequently Asked Questions

1. What is INSTR in SQL?

The INSTR function in SQL is used to search for the occurrences of a substring within one string and returns the position of the first occurrence. This is commonly used in SQL queries to perform a string manipulation task.

2. What is the main difference between an INSTR and SUBSTR () using examples?

The main point of difference between INSTR () and SUBSTR () lies in their functionalities.

• The INSTR () returns the position of a substring within any string
• The SUBSTR () returns a substring from a string that is based on the specified starting point and length.
• Example

- INSTR (‘hello world’. ‘lo’) returns 4
- SUBSTR (‘hello world’, 4.2) returns ‘lo’

3. What are the 4 parameters of INSTR () function?
   
   The four major parameters of an INSTR () function is:
   

• The string to search within
• The substring to search for
• The starting position (this is optional)
• The occurrence (this is optional)
  

4. What replace the INSTR in SQL ()?
   

• In a SQL server, the replacement for INSTR () function is CHARINDEX ()
• In PostgreSQL, it is POSITION ()
  

5. What is the INSTR () used for?

INSTR () function is used to search for all the occurrences of a substring within one string. Moreover, it helps in the tasks like extraction, manipulation & filtering based on some specific patterns.

6. What is the main difference between an INSTR () and indexOf ()?
   

• INSTR is a function used in SQL, whereas indexOf () is that function which is used in programming languages like JavaScript.
• Both the functions perform similar purposes but the syntax and usages of both may differ between SQL and programming languages.
  

7. What are the functions of INSTR () and SUBSTR ()
   

• An INSTR () function is used to find the position of a substring within any string.
• A SUBSTR () function is used to extract any substring from a string based on a specified starting position and length.