Difference between the patindex and charindex functions in SQL Server

The string search function is frequently used recently.

Including

1. fully match the search string
2. Fuzzy search for strings

Both the CHARINDEX and PATINDEX functions return the starting position of the specified mode. PATINDEX can use wildcards, but CHARINDEX cannot.
Both functions have two parameters:
1. the mode in which you want to obtain the location. With PATINDEX, the pattern is a literal string that can contain wildcards. Use CHARINDEX, which is a literal string (cannot contain wildcards ).
2 string value expression (usually the column name ).
For example, the search mode "wonderful" is located at the beginning of a specific row in the notes column in the titles table.Copy codeThe Code is as follows: USE pubs
Select charindex ('Wonderful ', notes)
FROM titles
WHERE title_id = 'tc3218'

For example, use the wildcard search mode "candies" to locate the start position in any row of the Description column in the Categories table:Copy codeThe Code is as follows: USE Northwind
GO
SELECT CategoryID, PATINDEX ('% candies %', Description) AS POSITION
FROM Categories
Where patindex ('% candies %', Description) <> 0

I hope it will be useful to you.

If you still do not understand the above, you can continue to look at it:

I. [SQL] Detailed description of patindex [Z]
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 %", e-xpression)
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.
E-xpression
An expression is usually used to search for columns in the specified mode. e-xpression 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.
Ii. SQL Server CHARINDEX and PATINDEX
If you have written many programs, you may occasionally encounter the need to determine whether the character or character string is contained in a piece of text. In this article, 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 many different character search problems.
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. Next we will take two examples to see 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 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

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.

Like the CHARINDEX function, the PATINDEX function returns the start position of the search string in the searched string. Suppose 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 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.