T-SQL PATINDEX() Function
The T-SQL (Transact-SQL) PATINDEX() function returns the location of the first occurrence of a specified pattern in a given string. If the pattern is not found within the given string, this function returns 0.
This function performs a case-insensitive search. Note that the first position in string starts with 1.
Syntax
PATINDEX(pattern, string)
Parameters
pattern |
Required. Specify the pattern to search for in string. |
string |
Required. Specify the string to search. |
Return Value
Returns the location of the first occurrence of the pattern in the string.
Example 1:
The example below shows the usage of PATINDEX() function.
SELECT PATINDEX('%pha%', 'AlphaCodingSkills.com'); Result: 3 SELECT PATINDEX('%ing%', 'AlphaCodingSkills.com'); Result: 9 SELECT PATINDEX('%l%', 'AlphaCodingSkills.com'); Result: 2 SELECT PATINDEX('%.com', 'AlphaCodingSkills.com'); Result: 18 SELECT PATINDEX('%.c_m', 'AlphaCodingSkills.com'); Result: 18 SELECT PATINDEX('%ABC%', 'AlphaCodingSkills.com'); Result: 0
Example 2:
Consider a database table called Employee with the following records:
PhoneNumber | EmpID | Address |
---|---|---|
+1-8054098000 | 1 | Brooklyn, New York, USA |
+33-147996101 | 2 | Grenelle, Paris, France |
+31-201150319 | 3 | Geuzenveld, Amsterdam, Netherlands |
+86-1099732458 | 4 | Yizhuangzhen, Beijing, China |
+65-67234824 | 5 | Yishun, Singapore |
+81-357799072 | 6 | Koto City, Tokyo, Japan |
In the query below, the PATINDEX() function is used to extract the country code from the PhoneNumber column records.
SELECT *, SUBSTRING(PhoneNumber, 1, PATINDEX('%-%', PhoneNumber) - 1) AS CountryCode FROM Employee;
This will produce the result as shown below:
PhoneNumber | EmpID | Address | CountryCode |
---|---|---|---|
+1-8054098000 | 1 | Brooklyn, New York, USA | +1 |
+33-147996101 | 2 | Grenelle, Paris, France | +33 |
+31-201150319 | 3 | Geuzenveld, Amsterdam, Netherlands | +31 |
+86-1099732458 | 4 | Yizhuangzhen, Beijing, China | +86 |
+65-67234824 | 5 | Yishun, Singapore | +65 |
+81-357799072 | 6 | Koto City, Tokyo, Japan | +81 |
❮ T-SQL Functions