In this post explains how to use the PATINDEX function in SQL Server (Transact-SQL) with syntax and examples.
In SQL Server (Transact-SQL), the PATINDEX functions returns the location of a pattern in a string. The search is not case-sensitive.
The syntax for the PATINDEX function in SQL Server (Transact-SQL) is:
PATINDEX( '%pattern%', string )
The pattern that you want to find. pattern must be surrounded by % characters. Other wildcard characters can be used in pattern, such as:
Wildcard | Explanation |
---|---|
% | Allows you to match any string of any length (including zero length) |
_ | Allows you to match on a single character |
[ ] | Allows you to match on any character in the [ ] brackets (for example, [abc] would match on a, b, or c characters) |
[^] | Allows you to match on any character not in the [^] brackets (for example, [^abc] would match on any character that is not a, b, or c characters) |
is the string to search within.
The PATINDEX function can be used in the following versions of SQL Server (Transact-SQL):
Let's look at some SQL Server PATINDEX function examples and explore how to use the PATINDEX function in SQL Server (Transact-SQL).
For example:
SELECT PATINDEX('%T_e%', 'AODBA.com');
Output: 7
SELECT PATINDEX('%A%com', 'AODBA.com');
Output: 2
SELECT PATINDEX('%[aeiou]%', 'AODBA.com');
Output: 2 (matches on the first a, e, i, o, or u character found)
SELECT PATINDEX('%z%', 'AODBA.com');
Output: 0