SQL Server charindex and patindex

If you have written a lotProgram, You may occasionally encounter whether to include a character or character string in a paragraph. In this articleArticleIn, I will discuss how to use the charindex and patindex functions to search for text columns and strings. I will show you how these two functions work and explain their differences. At the same time, some examples are provided. Through these examples, you can consider using these two functions to solve the issue of searching for many different characters.
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. Next, we will analyze these two functions one by one.

How to Use the charindex Function

The charindex function returns the starting position of a character or string in another string. The charindex function is called as follows:

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 that 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 ". Let's take a look at the following function command execution results:

Charindex (SQL, Microsoft SQL Server)

This function command returns the starting position of "SQL" in "Microsoft SQL Server". In this example, the charindex function returns position 11 of "S" in "Microsoft SQL Server.
Next, let's look at the charindex command:

Charindex (7.0, Microsoft SQL Server 2000)

In this example, charindex returns zero because the string "7.0" cannot be found in "Microsoft SQL Server. The following two examples show how to use the charindex function to solve the actual T-SQL problem.

In the first example, we assume that 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.
Contactname
------------------------------
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 first name and last name, so the substring function can separate the contactname column, 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 abbreviated RD. The selection statement is similar to this:

Select count (*) from northwind. DBO. MERs
Where charindex (Rd, address)> 0 or charindex (road, address)> 1

How to Use the patindex Function

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.

Similar to the charindex function, the patindex function returns the start position of the search string in the searched string. If there is such a patindex function:

Patindex (% BC %, ABCD)

The result returned by the patindex function is 2, which is the same as that returned by 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. Write the patinded function as follows:

Patindex (AB %, ABCD)

The result of executing this command returns 1, indicating 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 has all the 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. Check that SQL Server books online can 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 result.

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

You can now find the difference between charindex and patindex when searching strings. The patindex function supports wildcard characters, which can be used in a variety of search changes. Charindex cannot. Depending on your own situation, these two functions are very helpful for searching, controlling, and analyzing strings in SQL Server.

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/Hello_World_wusu/archive/2009/09/08/4533177.aspx