INSTR Function in SQL

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
Topics Covered

Overview

In the standard query language (SQL), the string function INSTR() returns the beginning or location of a substring or pattern within the supplied input string. Oracle/PL and MYSQL only support the INSTR() function. However, string functions to locate a substring are supported by other SQL database systems, including PostgreSQL and SQL Server. However, their syntax varies slightly. In PostgreSQL, SUBSTRING() is a function that is comparable to INSTR(), whereas CHARINDEX() is a function in SQL Server.

What is INSTR() in SQL?

INSTR functions look for sub-strings in a string, according to INSTR in SQL. The function outputs an integer indicating where the first character of this instance is located in the string.

From the characters in the input character set, INSTR creates strings. INSTRB uses bytes as opposed to characters.

INSTRC uses characters that are entirely in Unicode. INSTR2 uses UCS2 code points. Additionally, the UCS4 code point system is used by INSTR4.

Syntax of INSTR() in SQL

INSTR() in SQL Parameter Arguments

There are a few parameters that can be included in the INSTR() function. The parameters are:

  1. String
  2. Substring
  3. Start position
  4. Nth occurrence

String Argument:

The string types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB are all acceptable string types for the INSTR function in SQL, which falls under SQL String Functions .

An original string or piece of data is the string argument. You must compare the substring with the original string to determine whether it is present.

Substring Argument:

A substring is a portion of a string that is present in the original string, according to the SQL function INSTR. The substring in the string to be searched for and any of the following are valid substrings: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Starting Position:

According to INSTR, it is an option in SQLIt. The starting point for the search is in the string. In case it is not supplied, it defaults to 1. The string's first position is the number one. Before looking for the start position of several characters, the INSTR function counts the back start position and the number of characters from the string's end if the start position is negative.

Nth Occurrence Argument:

It's an option, according to SQL's INSTR function. If this is the nth time a substring has appeared, it defaults to 1 if it is not specified. This might allow you to determine how frequently the original string may contain that substring.

Return Value of INSTR()

A numerical value is returned by the INSTR function. The string's first position is 1. The INSTR function will return 0 if a substring is not present in the string.

Code:

Output:

Code:

Output:

Code:

Output:

Code:

Output:

Code:

Output:

Examples of INSTR String Function

Basic Working of INSTR() Function

Being a query language, SQL comes with several built-in methods to deal with the database's String data values.

The SQL INSTR() method is one such amazing function.

The SQL INSTR() function locates a string's or a character's initial appearance in another string. The position of any string or substring's initial or first appearance in another string data value is therefore observed by the INSTR() function.

Let's move on to the structure and syntax of the SQL INSTR() function now that we have a better understanding of how it operates.

Display the Index of the First Occurrence of a Character of Each Data Value Present in the Column

The SQL INSTR() method in the example below looks for the character 'P' in the input string data value on its first occurrence.

Output:

The INSTR() function also works with string values in addition to looking for characters in a string only once.

In this case, we've looked for the first instance of the string "JournalDev" in the input data value, and we're returning its position value.

Output:

The INSTR() function returns zero (0) if the string or character being searched for does not exist or is not present in the string.

Output:

Conclusion

  1. Several built-in functions in SQL can be utilized for a wide range of activities. The ability of functions to convert the case of strings, perform in-string or substring operations, perform mathematical operations on numeric data, and perform data operations on date-type values are all essential features. User parameters are optional for SQL functions, and they are required to return a value.
  2. Identifies the first occurrence of a character or string in another string. As a result, the INSTR() function keeps note of where any string, sub-initial, or first string appears in the meaning of another string.
  3. There are many SQL String Functions in SQL Server. You can edit each character in a string separately, compare strings, search strings, extract substrings, and copy strings using these tools. It is also possible to change the case of strings using these SQL String Functions.
  4. To determine whether a substring is present in the original string, use INSTR(). If a string is present, it indicates where in the original string the substring is located relative to the index point. The result also includes the number of times the substring appears. It returns 0 if the substring is absent from the starting string.
  5. The SQL language has been around for a while, but organizations all around the world still value it today. Mastering SQL is crucial to showcasing a robust skill set because it is frequently one of the most in-demand tech skills.