Patindex () Usage

Patindex
Returns the starting position of a mode that appears for the first time in a specified expression. If this mode is not found in all valid text and character data types, zero is returned.
Syntax
Patindex ('% pattern %', expression)
Parameters
Pattern
A string. Wildcards can be used, but must contain % characters before and after the pattern (except when the first and last characters are searched ). Pattern is the expression of the short character data type category.
Expression
An expression is usually used to search for columns in the specified mode. expression is a string data type.
Return type
Int
Note
Patindex is useful for text data types. Except for is null, is not null, and like (these are the only comparative operations that are valid for text in the WHERE clause, patindex can also be used in the WHERE clause.

-----------------------------------------

Example 1:
Find out that the description field in the northwind. DBO. Categories table contains all records containing the word "Bread" or "Bread". Then the selection statement may be like this:

Select description from northwind. DBO. Categories
Where patindex ('% [B, B] Read %', description)> 0
In patindex, wildcards can be used to determine the upper and lower case "B"

Example 2:
Find out that the description field in the northwind. DBO. Categories table contains the word "Bread" or "Bread", and the second sub-letter is not the record of "E.

Select description from northwind. DBO. Categories
Where patindex ('% [B, B] Read %', description)> 0 and patindex ('_ [^ e] %', description) = 1
By adding a patindex function using the ^ wildcard in the Condition Statement, We can filter out the "dessert, candies, and sweet breads" record. The preceding query results have only one record.

Patindex and charindex

the patindex function supports wildcard characters and can be used in many different searches. Charindex cannot. Depending on your own situation, these two functions are very helpful for searching, controlling, and analyzing strings in SQL Server.