Getting Started With SQL Function PATINDEX and Its Use Cases

Today, I will explain a SQL Server string function PATINDEX () used to return an integer value representing the starting position of the first occurrence of a pattern in a specified expression. This function will return zero if the pattern is not found in the specified expression.

The syntax of this function is given below.

Here,

• Pattern is a character expression for which the start position will be returned. We can also use wildcard characters like

• Expression is either an input string or column name in which a specified pattern will be searched to get its starting position.

If you specify NULL to any of the above two arguments, the T-SQL function PATINDEX will also return NULL in the output. Although PATINDEX uses the collation of input expression for the comparison, if you want to perform a comparison in a specified collation, you can also do it by specifying COLLATE to apply an explicit collation of the input.

Use Cases of SQL Function PATINDEX

Next, I will demonstrate various use cases of this function in the below section. You can use SQL Server management studio or any third-party tools like dbForge SQL tools to practice these use cases. This SQL Tool provides many essential features that help Microsoft SQL Server developers.

Use Case 1: Use PATINDEX With an Input String

Let’s start with its first use case in which we will specify an input string and then search for a pattern from the specified input string. I have specified the input string as “My name is Manvendra Deo Singh” and the pattern as “Deo” to get its starting position in this string. Starting position will be calculated from 1st character as number 1 and will keep on going in increasing order.

I have shown how each character is assigned a number for its position, counted in the above table. Here numbers are assigned to each character, including white space. 

Let’s run below T-SQL statement to return starting position of string pattern “Deo.”

You can see its output in the below image, showing as 22. Now, you can see the starting position of this string, “Deo.” I have highlighted its starting position in the above table, showing 22.

We can take another example using the same T-SQL statement by just making changes in the pattern string. Now, I have searched the starting position of pattern strings “My,” “M,” and “Manvendra.” 

 You will get below the output of this query in which the first 2 searches are showing the same result as 1 because M is the first character of this string.

Next, I will be showing the result of this query if we use NULL as a pattern string. As I have said above, if we specify NULL for this argument, our output will also be returned as NULL. So, run the below statement to get the output.

-- use PATINDEX with NULL

SELECT PATINDEX (NULL, ‘My name is Manvendra Deo Singh’ ) 

We can see the output of the above query is also showing as NULL.

Let’s take another example in which we will search a pattern string that is not part of the specified string and see its output which should come as 0.p

I have searched the “SQL” pattern string to get its starting position from the specified input string. Unfortunately, the output of the above query is showing as 0 because the “SQL” string is not there in the specified input.

Use Case 2: Use PATINDEX With a Column

I have explained various examples of this function based on specifying the input string in the first use case. Now, I will show you other examples based on specifying a table column. As I mentioned above, we can also use this function on a column to display the desired result set for all rows stored on that column. 

Let’s take an example in which I have used the PATINDEX SQL function in the sys.databases table to find the position of a pattern in the collation_name column.

I have executed the above SQL statements and got the below output. I am searching for the pattern “CI_AS” position in the collation_name column. We can see the difference in the result set. One database is running on a collation type in which CI_AS is not there, whereas others have this string at different positions, which are given in the “pattern position” column of the below output.

You can change the expressions and get the desired result for your data stored in a column.

Use Case 3: Use PATINDEX With Wildcards

This section will help you understand various use cases of PATINDEX with wildcards. As mentioned above, we can use wildcards as patterns to return the desired result set. Have a look at the wildcards, which can be used to return the output for various uses.

• We use

• We can use _ if we need to match a single character

• We can use [] If you need to match any character mentioned in the brackets

• Finally, [^] is used to match any character which is not in the [^]

Using these wildcards in the PADINDEX function to get the output based on the above explanation. I have already shown

I have used _ (underscore) and

Look at the output; the first statement returns the starting position of string My, whereas the second statement returns the starting position of string Manvendra.

Let’s test a similar scenario on the column name of a system table in the below example.

Here we are searching for starting position of the string, which is starting with the letter C with an underscore wildcard in the collation name column. If you look at the first 2 rows of the below output, starting position has returned the same, whereas both are for different strings. 

Use Case 4: Use PATINDEX in the WHERE Clause

We can also use this string function in the WHERE clause to return specific results based on the matching criteria defined by this function. Again, I use the system table sys.databases table to demonstrate this use case.

Suppose we want to get the list of the databases whose collations have a numeric letter placed at the position of more than the 10th character. The second statement displays a similar result where collations have a numeric letter placed at the position of more than the 9th character.

Have a look at the output of both statements.

Below is another example where I am searching the list of all databases with the character “db” in their names after the 4th character. The second statement shows the list of databases with a numeric value in their names after the 4th character.

Here is the output, and here we can see and validate them. For example, the first result shows all databases whose names have DB characters after the 4th character position. In contrast, the second output shows that we have only one database with a numeric value in their names after the 4th character.

Conclusion

We learn SQL function PATINDEX and its use cases in this article. Try these use cases to practice using this SQL function in your requirements. Please share your questions and feedback in the comment section so that we can read your concerns and respond accordingly.