In this SQL Server tutorial, you will understand how to use the PATINDEX function in SQL Server. It is helpful in data manipulation and retrieval.
First, you will understand the definition of the PATINDEX() function with syntax; then, with different examples, you will learn how to find the index of the specified pattern in the string.
After that, you will use the wildcard characters like in the LIKE operator with the PATINDEX function in SQL Server. Then, you will learn how to pass the pattern to this function using the variable.
Finally, you will use the PATINDEX function in SQL Server with a real table, where you will deal with scenarios which help you understand how to extract the domain from the email using the PATINDEX() function.
PATINDEX Function in SQL Server
The PATINDEX function in SQL Server returns the starting index of the specified pattern (substring) in a string.
Let me explain it to you with a simple example. Let’s say you have the string ‘Learning SQL Server’, and I want you to write the index number below each letter from 1 to the length of a string, including spaces.
You need to find the substring ‘SQL’ starting position in that string. If you look at the index number for the letter ‘S’ in SQL, which is 10, so the substring ‘SQL’ starts from the index number 10.
That’s what the PATINDEX() function does; it returns the starting index of the substring in the given string.
The syntax of PATINDEX() is given below:
PATINDEX ( '%pattern%' , string_value )Where,
- PATINDEX(): This is the function which returns the starting position of the pattern in the string.
- %pattern%: It is the character string or substring that you want to find in the string, and it should surrounded by %. Also, with the pattern, you can specify the wildcards as LIKE, such as:
- Underscore (_) to match a single character.
- Brackets [] to match any characters inside it.
- [^] means do not match any character inside it.
- % to match a string of any length.
- string_value: This is the source string where the starting position of the substring is found.
The return type of the PATINDEX function in SQL Server is int, and if the expression is of type varchar(max) and nvarchar(max), then the return type is bigint.
Now if you have any doubt about the PATINDEX() function or how it works, you will understand through an example.
For example, Benjamin Franklin’s famous quote, ‘Where liberty dwells, there is my country’, and in this string, you must find the starting position of the pattern (substring) ‘dwells’.
So, pass this string and pattern to the PATINDEX() function, as shown in the query below.
SELECT
PATINDEX('%dwells%','Where liberty dwells, there is my country');
From the output, the pattern ‘dwells’ position is 15 in the string ‘Where liberty dwells, there is my country’.
You can even find the starting position of any substring in that string. But remember, this function returns the starting position of the first character of the pattern in the string.
To verify the above-said statement, just run the query below.
SELECT
PATINDEX('%d%','Where liberty dwells, there is my country');
From the output, the PATINDEX() returns the starting position of the pattern ‘d’ as 15, but this time, we haven’t passed the pattern as ‘dwells’ but still got the same result.
When you pass the pattern as ‘dwells’. In reality, it returns the index or position of the first character in that pattern within a string, which is why it is called PATINDEX(), because this function returns the starting position of the pattern in the given string.
Well, now you know about working of the PATINDEX function in SQL Server. Let’s see how to use the wildcards characters with the PATINDEX() function.
Wildcard with PATINDEX Function in SQL Server
You can also use the wildcard characters with the PATINDEX function in SQL Server; the wildcard character is the way to substitute one or more than one character in a string.
You can use several wildcards with the PATINDEX() function, which you have already learnt while going through the syntax of the PATINDEX() function, but if you don’t know, then look at the wildcards given below.
- Underscore (_) to match a single character in the string.
- Brackets [] to match any characters inside it.
- [^] means do not match any character inside it.
- % to match a string of any length.
Let’s take an example based on each wildcard character.
Using the Underscore (_) with PATINDEX Function in SQL Server
The wildcard underscore (_) represents the single characters, which means matching a single character in the string. If you don’t know about a single character in a string, you can represent that unknown character with this (_) character.
For example, in the string ‘I work from Mon to Fri’, you need to find the starting of the Mon substring. Run the query below and look where wildcard (_) is used in the pattern.
SELECT
PATINDEX('%_on%','I work from Mon to Fri');
Now look at the pattern ‘%_on%’ within PATINDEX() function; here, we haven’t provided the complete pattern as ‘Mon’; instead, we only provided the pattern as ‘_on’, which contains the wildcard (_) at the beginning of the pattern.
This (_) represents the letter ‘M’ in the pattern, so here you need to understand when you don’t know about the character in the pattern (substring), but you know the length of the pattern (substring), then at the place of unknown character, you can use the (_) wildcard character.
Using the Brackets [ ] with PATINDEX Function in SQL Server
Another wildcard character is a bracket [ ]. If you want to find the starting position of only the substring within brackets [ ], use this wildcard character in your PATINDEX function in SQL Server. This is the complex wildcard expression.
For example, take this same string but find the starting position of the pattern ‘or’ using the query below.
SELECT
PATINDEX('%[or]%','I work from Mon to Fri');
As you can see, the starting position of the pattern ‘[or]’ is 4; here, the substring is within brackets, which means only finding the starting position of this substring in the brackets.
If the string contains the number and you want to find the starting number only, then you can specify to use brackets, as shown in the query below.
For example, if you want to find the starting position of the number 9 in a string, ‘I work from 9:00 Am’.
SELECT
PATINDEX('%[0-9]%','I work from 9:00 Am');![Using the Brackets [ ] with PATINDEX Function in SQL Server to Find Index of Number in String](https://sqlserverguides.com/wp-content/uploads/2024/01/Using-the-Brackets-with-PATINDEX-Function-in-SQL-Server-to-Find-Index-of-Number-in-String.jpg)
Here, the query returns the starting index of the number 9, which is 13. Look in the brackets [0-9]. We have specified the expression ‘0-9’, which means to match any number from 0 to 9 within a string and return the starting index of the first matching number.
Similarly, for any character from a to z, you can use the brackets like this [a-z], or for A To Z, use [A-Z].
This is how you can use the brackets [ ] as wildcards with the PATINDEX function in SQL Server.
Using the String Operator [^] with PATINDEX Function in SQL Server
The [^] string operator is opposite of the [ ] brackets, so whatever substring is specified within the string operator is not searched within the string.
In simple words, the string operator, which is a wildcard, matches any single character that is not in range or not specified within it.
For example, you have a string like this: ‘Need help at street 234!’ as you can see, this string contains a letter, number and an exclamation mark (!), so here you need to find the starting index of the pattern exclamation mark (!).
For that, execute the query below.
SELECT PATINDEX('%[^ a-zA-Z0-9]%','Need help at street 234!');
The starting position of the exclamation mark is 24, and here, the wildcard string operator [^] is passed with the expression a-zA-Z0-9. This means returning the starting position of the substring or pattern except for the letter (which is small and caps) and number.
In the string, there is one character which is not a letter and number, and that is an exclamation mark (!); as you can see, its starting index number is 24.
This is how to use the string operation with the PATINDEX function in SQL Server.
PATINDEX Function in SQL Server: Specify Pattern using Variable
Until now, we have passed the pattern directly into the PATINDEX() function. However, you can also specify the pattern through a variable, which means storing the pattern in a variable and passing that variable to the PATINDEX() function.
For example, you have the ‘Working on SQL Server Database’ string. Here, you need to find the starting position of the pattern ‘on’, but you need to pass this pattern to the PATINDEX() function through a variable.
So first, declare variable name @pattern of type varchar(10) and assign a pattern value, ‘ on’, as shown in the query below.
DECLARE @pattern VARCHAR(10) = 'on';
SELECT PATINDEX('%' + @pattern + '%','Working on SQL Server Database');
The starting index of the pattern ‘on’ in the string is 9. But most importantly, here, you need to understand how the pattern is passed to the PATINDEX() function.
Look at the pattern, passed as ‘%’ + @pattern + ‘%’; the variable @pattern is passed as a pattern.
To pass the variable as a pattern to the PATINDEX() function, surround it with the percent symbol ‘%’ in a single quote and concatenate the variable with the percent symbol using the plus operator (+).
You can use this structure ‘%’ + variable_name + ‘%’ to pass a pattern through the variable to PATINDEX() function. Here, the variable name can be any variable containing the pattern whose starting position you want to find the string.
This is how to pass the pattern through variables in the PATINDEX function in SQL Server.
Using PATINDEX Function in SQL Server on Table
Let me show you how to use the PATINDEX function on a table column; for example, you have a Users table, as shown below.
Using the information in the above table, you must parse users’ email to extract the domain. For that, use the query below.
SELECT SUBSTRING(email, PATINDEX('%@%', email) + 1 , 20) AS Domain
FROM Users;
After executing the above query, the domain is extracted from the user’s email using the SUBSTRING() and PATINDEX functions in SQL Server.
Let’s understand the quer part, SUBSTRING(email, PATINDEX(‘%@%’, email) + 1 , 20).
Here, the SUBSTRING() function extracts the domain from the email of length 20 from the position that the PATINDEX() specifies; this function returns the starting index of the pattern ‘@’ for each email by adding one extra integer value to the returned index number.
The returned starting position by the PATINDEX() function becomes the starting position for the SUBSTRING(), from where it begins to extract the substring of length 20. Thus it extracts the domain, for example, adventure.com, explorer.net, etc.
Conclusion
In this SQL Server tutorial, you have learned how to find the starting position or index of the pattern (substring) in the given string using the PATINDEX function in SQL Server.
Then, with the PATINDEX() function, you have used the different wildcard characters such as [ ], [^], and underscore (_). After that, you have passed the pattern through the variable to the PATINDEX() function.
Ultimately, you have extracted the domain name from the user’s email using the combination of SUBSTRING() and PATINDEX() functions.
You may like to read:
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.