SQL Server charindex and patindex

Use environment: determines whether a string is contained in a paragraph of text.

Usage: The charindex and patindex functions are often used to search for characters or strings in a string. If the searched characters contain the characters to be searched, the two functions return a non-zero integer, which is the start Number of the characters to be searched in the searched characters. The patindex function supports wildcard characters for search. However, charindex does not support wildcard characters (the difference between the two ).

Example:

1: Use of charindex

The charindex function returns the starting position of a character or string in another string.
Charindex function call format: charindex (expression1, expression2 [, start_location])

Expression1 is the character to be searched in expression2. start_location is the position where the charindex function starts to find expression1 in expression2.
The charindex function returns an integer. The returned integer is the position of the string to be searched in the string to be searched. If charindex does not find the string to be searched, the function integer is "0"

Select charindex ('SQL', 'SQL Server 2008') // return 1
Select charindex ('20140901', 'SQL Server 123') // return 0

Suppose you want to display the last name of the contact column in the first five rows of the northwind database customer table. This is the first five rows of data.
Maria Anders
Ana Trujillo
Antonio Moreno
Thomas hard
Christina Berglund

As you can see, mmname contains the customer's first name and last name, which are separated by a space. I use the charindx function to determine the spaces in the two names. Through this method, we can analyze the space position of the contactname column, so that we can only display the last name part of this column. This is the record showing the last name of the first five rows in the customer table of northwind!

Select top 5 substring (contactname, charindex ('', contactname) + 1,
Len (contactname) as [last name] From northwind. DBO. MERs

The following is the output result of this command.
Last name
------------------------------
Anders
Trujillo
Moreno
Hard
Berglund

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. I add 1 to the integer returned by the charindex function, so that the last name does not start with a space.

In the second example, you want to calculate the number of all records in a field containing a specific character. The charindex function can easily solve your problem. 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

2: Use of patindex

The patindex function returns the starting position of a character or string in another string or expression. The patindex function supports searching strings with wildcards, which makes the patindex function very valuable for changing search strings. The command for the patindex function is as follows:

Patindex ('% pattern %', expression)

Pattern is the string to be searched, and expression is the string to be searched. Generally, expression is a field in a table. "%" must be used before and after pattern unless the string you search is at the beginning or end of the string to be shrunk. Like the charindex function, the patindex function returns the start position of the search string in the searched string.

Patindex ('% BC %', 'abc') // return 2

This is the same as the charindex function. Here, the % Mark tells the patindex function to find the string "BC", regardless of the number of characters in the searched string before and after "BC!
If you want to know whether the searched string starts with a specific string, you can skip the % mark.
The patinded function is written as follows: patindex ('AB %', 'abcd') // return 1 indicates that the searched string "AB" is found in the searched string "ABCD.

You can use wildcards to edit search strings that are much more complex than the preceding simple example. If you want to determine whether a string contains letters A and Z and any numbers, the parindex FUNCTION command may look like this:

Patindex ('% [A, Z, 0-9] % [A, Z, 0-9] % [A, Z, 0-9] %', 'xyzabc123 ')

Note that many wildcard characters are used in the search character section in the preceding example. View the SQL Server books online to get more information about the pass. Next, we will use two examples to see how patindex and select can be used together.

Assume that the description field in the northwind. DBO. Categories table contains all records containing the word "Bread" or "Bread", the selection statement may be as follows:

Select description from northwind. DBO. Categories
Where patindex ('% [B, B] Read %', description)> 0

Here, I use wildcards to determine the upper and lower case "B ". After I run this script in the notthwind database, the following result is displayed:
Description
--------------------------------------------------------
Desserts, candies, and sweet breads
Breads, crackers, pasta, and cereal

This is an example of using an additional wildcard to find some records. In this example, the second letter of the description field is not an "E" record in the preceding query results.

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.
Description
--------------------------------------------------------
Breads, crackers, pasta, and cereal

Summary

Now you can see the difference between charindex and patindex when searching strings. 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.

 

Reference: http://songjg2010.javaeye.com/blog/754835