The PATINDEX function in SQL Server is used to return the starting position of a specified pattern in a specified string. If the pattern is not found, it returns 0.
Syntax
PATINDEX ( 'pattern' , expression )
- pattern: A string expression that defines the pattern you want to search for. You can use wildcard characters (like % and _) to specify the pattern.
- expression: The string expression that is searched for the specified pattern.
Wildcard Characters
- % matches any string of zero or more characters.
- _ matches any single character.
Example Usage
1. Find the position of a simple substring:
DECLARE @string NVARCHAR(100) = 'Hello, World!';
SELECT PATINDEX('%World%', @string) AS Position;
This will return 8 because the substring "World" starts at the 8th position.
2. Using wildcard patterns:
DECLARE @string NVARCHAR(100) = 'abc123def456';
SELECT PATINDEX('%[0-9]%', @string) AS Position;
This will return 4 as it finds the first occurrence of a digit (0-9) starting at position 4.
3. Searching with the underscore wildcard:
DECLARE @string NVARCHAR(100) = 'File_XYZ.txt';
SELECT PATINDEX('File_._YZ%', @string) AS Position;
This will return 1 since the pattern matches "File_XYZ.txt", where the underscore (_) matches any single character.
4. Pattern not found:
DECLARE @string NVARCHAR(100) = 'Hello, World!';
SELECT PATINDEX('%Goodbye%', @string) AS Position;
This will return 0 since "Goodbye" is not present in the string.
Notes
- The search is case-insensitive by default if the collation of the database or column is case-insensitive.
- PATINDEX is particularly useful when you need to search for patterns in strings and can be combined with other string functions in SQL for more complex string manipulations.
This function offers flexibility and power in querying and manipulating strings in SQL Server.