PATINDEX Function in SQL

The PATINDEX string function in Structured Query Language returns the position of the specified pattern in the original string. If Sub-string is omitted in the original string, the PATINDEX function returns 0. The first position of the original string is indicated as 1.

Syntax of PATINDEX String Function

Syntax1: This syntax uses the PATINDEX function with the column name of the SQL table:

In the syntax, we have to specify the name of that column on which we want to find the position of string.

Syntax2: This syntax uses the PATINDEX function with the string:

Examples of PATINDEX String function

Example 1: The following SELECT query shows the PATINDEX of the character S in the original string:

Output:

Example 2: The following SELECT query shows the PATINDEX of the DELHI word in the given string:

Output:

Example 3: The following SELECT query returns the PATINDEX of the 'Manufacturing Company' string in the original string:

Output:

Example 4: This example uses the PATINDEX function with the table in Structured Query Language.

The syntax for creating the new table in the SQL database is as follows:

The following CREATE statement creates the Student_Grade table:

The below INSERT queries insert the records of students with grades and marks in the Student_Grade table:

The following SELECT statement displays the inserted records of the above Student_Grade table:

Query 1: The following SELECT query uses the PATINDEX function with the First_Name column of the above Student_Grade table:

This SQL statement shows the PATINDEX of 'a' character in the first name of each student.

Output

Query 2: The following SELECT query uses the PATINDEX function with the Last_Name column of the above Student_Grade table:

This SQL statement shows the PATINDEX of the 'S' character in the last name of each student.

Output:

Query 3: The following SELECT query uses the PATINDEX function with the First_City column of the above Student_Grade table:

This SQL statement shows the PATINDEX of the 'ow' character in the first city of each student.

Output:

Query 4: The following SELECT query uses the PATINDEX function with the Second_City and New_City column of the above Student_Grade table:

Output: