SQL Server – PATINDEX() Function
The PATINDEX() function in the SQL server is used to return the starting index of the first occurrence of a pattern in a string or a specified expression.
- It returns zero if the pattern is not found.
- It returns NULL if either pattern or expression is NULL.
- Starting index is 1.
PATINDEX ( '%pattern%' , expression )
There are two parameters and both are required.
- Pattern – This is the sequence to be found in the string that must be surrounded by %. Other wildcard characters can be used in patterns. The pattern has a limit of 8000 characters. Other wildcard characters are ‘%’ , ‘-‘ , ‘’ , ‘[^]’.
- Expression – A string that is searched for the specified pattern.
The function PATINDEX() is applicable to the following databases.
- SQL Server (all supported versions)
- Azure SQL Database
- Azure SQL Data Warehouse
- Azure SQL Managed Instance
- Azure Synapse Analytics Analytics Platform System (PDW)
- Parallel Data Warehouse
Suppose if we want to find the first occurrence of ‘ek’ in the string ‘GeeksforGeeks’.then query will be like:
SELECT PATINDEX('%ek%', 'GeeksforGeeks');
Now if we finding the first occurrence of the letter ‘z’ in the string ‘GeeksforGeeks’.
SELECT PATINDEX('%[z]%', 'GeeksforGeeks');
It returns 0 because the given string does not contain the letter ‘z.
Take another example for finding the first occurrence of the symbol in the string ‘How are you?’.
SELECT position = PATINDEX('%[^ 0-9A-z]%', 'How are you?');
My Personal Notes arrow_drop_up
Please Login to comment...