Usage of the SQL statement charindex to compare charindex and patindex

Charindex
Returns the starting position of the specified expression in the string.
Syntax
Charindex (E & shy; xpression1, E & shy; xpression2 [, start_location])
Parameters
E & shy; xpression1
An expression that contains the order of characters to be searched. E & shy; xpression1 is a short character data type classification expression.
E & shy; xpression2
An expression is usually a column used to search for a specified sequence. E & shy; xpression2 belongs to the string data type classification.
Start_location
In E & shy; xpression2, search for the start character position of E & shy; xpression1. If start_location is not given, but a negative number or zero value, the search starts from the starting position of E & shy; xpression2.
Return type
Int
Note
If one of E & shy; xpression1 or E & shy; xpression2 belongs to the Unicode data type (nvarchar or nchar), the other is converted to the Unicode data type.
If one of E & shy; xpression1 or E & shy; xpression2 is null, charindex returns NULL when the database compatibility level is 70 or greater. If the database compatibility level is 65 or less, charindex returns NULL only when both E & shy; xpression1 and E & shy; xpression2 are null.
If E & shy; xpression1 is not found in E & shy; xpression2, charindex returns 0

Example 1:

Customname contains the customer's first name and last name, which are separated by a space. The charindx function is used to determine the spaces in the two names. Through this method, we can analyze the space position of the contactname column, so that only the last name part of this column can be displayed.

Select top 5 substring (contactname, charindex ('', contactname) + 1, Len (contactname) as [last name] from MERs

The charindex function finds the space between the first name and last name. Therefore, the substring function can separate the contactname columns so that only the last name is selected. Add 1 to the integer returned by the charindex function, so that the last name does not start with a space.
Example 2:
Calculate the number of records in which the addresses field in the northwind. DBO. Customer table contains the word road or its abbreviation RD. The selection statement is similar to the following:
Select count (*) from northwind. DBO. MERs
Where charindex ('RD ', address)> 0 or charindex ('road', address)> 1

 

For example, in the search mode "wonderful ",TitlesTableNotesThe start position in a specific row of a column.

Use pubs

Select charindex ('Wonderful ', notes)

From titles

Where title_id = 'tc3218'

For example, use the wildcard search mode "candies"CategoriesStart position of any row of the description column in the table:

 
Use northwindgoselect categoryid, patindex ('% candies %', description) as positionfrom categorieswhere patindex ('% candies %', description) <> 0
 

If no limit is imposed on the rows to be searched, the query returns all rows in the table and reports a non-zero value for all rows in this mode.

Patindex pairTextData types are useful. Except is null, is not null, and like (these areTextIn addition to only other comparative operations that are valid for the type), patindex can also be used in the WHERE clause.