T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

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:

PhoneNumberEmpIDAddress
+1-80540980001Brooklyn, New York, USA
+33-1479961012Grenelle, Paris, France
+31-2011503193Geuzenveld, Amsterdam, Netherlands
+86-10997324584Yizhuangzhen, Beijing, China
+65-672348245Yishun, Singapore
+81-3577990726Koto 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:

PhoneNumberEmpIDAddressCountryCode
+1-80540980001Brooklyn, New York, USA+1
+33-1479961012Grenelle, Paris, France+33
+31-2011503193Geuzenveld, Amsterdam, Netherlands+31
+86-10997324584Yizhuangzhen, Beijing, China+86
+65-672348245Yishun, Singapore+65
+81-3577990726Koto City, Tokyo, Japan+81

❮ T-SQL Functions