The string lookup feature has recently been used frequently.
Including
1. Full matching Find string
2. Fuzzy Lookup string
Both the CHARINDEX and PATINDEX functions return the start position of the specified pattern. PATINDEX can use wildcard characters, and CHARINDEX is not available.
Both of these functions have 2 parameters:
1 The pattern that you want to get its location. With PATINDEX, the pattern is a literal string that can contain wildcard characters. Using CHARINDEX, the pattern is a literal string (cannot contain wildcard characters).
2 string Value expression (usually the column name).
For example, the lookup mode "Wonderful" is the starting position in a particular row of the notes column in the titles table.
Copy Code code as follows:
Use pubs
SELECT CHARINDEX (' wonderful ', notes)
From titles
WHERE title_id = ' TC3218 '
For example, use wildcards to find the starting position of the pattern "candies" in any row of the Description column in the Categories table:
Copy Code code as follows:
Use Northwind
Go
SELECT CategoryID, PATINDEX ('%candies% ', Description) as POSITION
From Categories
WHERE PATINDEX ('%candies% ', Description) <> 0
Hope to be useful to everyone.
If the above still does not understand, you can continue to look down:
First, [SQL] patindex detailed explanation [Z]
PATINDEX
Returns the starting position of the first occurrence of a pattern in the specified expression, or zero if the pattern is not found in all valid text and character data types.
Grammar
PATINDEX ("%pattern%", e-xpression)
Parameters
Pattern
A string. You can use wildcard characters, but you must have a% character before and after pattern (except when searching for the first and last characters). Pattern is an expression of the short character data type category.
E-xpression
An expression, typically a column in which to search for a specified pattern, e-xpression as a string data type category.
return type
Int
Comments
PATINDEX is useful for text data types, and PATINDEX can also be used in the WHERE clause, in addition to is NULL, being not NULL, and like, which are the only other comparison operations that are valid for the text type in the WHERE clause.
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Example one:
If you find all the records in the Description field in the Northwind.dbo.Categories table that contain the word "Bread" or "Bread," then the selection statement might be:
Select Description from Northwind.dbo.Categories
Where patindex ("%[b,b]read%", description) > 0
Wildcard characters can be used in PATINDEX to determine uppercase and lowercase "b"
Case TWO:
Find out if the Description field in the Northwind.dbo.Categories table contains the word "Bread" or "Bread" and the second letter is not "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 a conditional statement, we can filter out the record "dessert, candies, and sweet breads". The query results above have only one record.
Patindex and Charindex
The PATINDEX function supports the use of wildcard characters and can be used in a number of changing lookups. and charindex not to be. Depending on your situation, these two functions are useful for searching, controlling, and analyzing strings in SQL Server.
Second, SQL Server charindex and patindex detailed
If you have written many programs, you may occasionally encounter the need to determine whether a character or character string is included in a text, and in this article I will discuss using the CHARINDEX and PATINDEX functions to search for text columns and strings. I'll tell you how these two functions work, explaining their differences. At the same time provide some examples, 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 single word. If the character being searched contains a character to search for, then the two functions return a Non-zero integer that is the number of digits in the searched character that the character is searching for. The PATINDEX function supports searching using wildcard characters, but Charindex does not support wildcards. Next, we analyze the two functions individually.
How to use the CHARINDEX function
The CHARINDEX function returns the starting position of a character or string in another string. The CHARINDEX function calls the following methods:
CHARINDEX (expression1, expression2 [, Start_location])
Expression1 is the character to look for in expression2, start_location is the CHARINDEX function that begins to find expression2 in expression1.
The CHARINDEX function returns an integer that returns an integer that is the position of the string to find in the string being searched. If CHARINDEX does not find the string to find, then the function integer "0". Let's take a look at the results of the following function command execution:
CHARINDEX ("SQL", "Microsoft SQL Server")
This function command will return to the start of SQL in Microsoft SQL Server, in which case the CHARINDEX function will return "S" to position 11 in Microsoft SQL Server.
Next, let's look at this 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. Here are two examples to see how to use the CHARINDEX function to solve the actual T-SQL problem.
The first example assumes that you want to display the last Name of the first 5 line of contact columns in the Northwind database Customer table. This is the first 5 lines of data
ContactName
------------------------------
Maria Anders
Ana Trujillo
Antonio Moreno
Thomas Hardy
Christina Berglund
As you can see, the customname contains the customer's name of the ' name ' and ' last ', separated by a space. I use the CHARINDX function to determine the position of the middle space of two names. In this way, we can analyze the space position of the ContactName column so that we can display only the last name part of the column. This is the record for the first 5 lines of the Customer table showing Northwind!
Select Top 5 substring (Contactname,charindex ("", ContactName) +1,
Len (ContactName)) as [last Name] from Northwind.dbo.customers
The following is the result of this command output.
Last Name
------------------------------
Anders
Trujillo
Moreno
Hardy
Berglund
The CHARINDEX function finds a space between the name of a and last, 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, if you want to calculate a record, the number of records in a field that contains a particular character. The CHARINDEX function can solve your problem conveniently. Calculates the number of records in the Addresses field in the Northwind.dbo.Customer table that contain the word road or its abbreviated RD, which is similar to the selection statement:
SELECT Count (*) from Northwind.dbo.Customers
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, and the PATINDEX function supports the use of wildcard characters in the search string, which makes the PATINDEX function valuable for the variable search string. The commands for the PATINDEX function are as follows:
PATINDEX ("%pattern%", expression)
Pattern is the string you are searching for, expression is the string you are searching for. In general, expression is a field in a table that needs to be labeled "%" before and after pattern, unless you are searching for a string that is at the front or end of the shrinking string.
Like the Charindex function, the PATINDEX function returns the starting position of the search string in the searched string. If there is such a patindex function:
PATINDEX ("%bc%", "ABCD")
The result of this patindex function is 2, which is the same as the CHARINDEX function. The% tag here 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 if the searched string starts with a specific string, you can omit the% mark above. The patinded function should write this:
PATINDEX ("ab%", "ABCD")
The result of this command returns 1, which means that the search string "AB" is found in the searched string "ABCD".
Using wildcards, you can edit a search string that is much more complex than the simple example I mentioned above. If you want to determine whether a string contains letters A and z, and any numbers, this parindex function command might look like this:
PATINDEX ("%[a,z,0-9]%[a,z,0-9]%[a,z,0-9]%", "XYZABC123")
Note that many of the wildcard characters are used in the search character section of the above example. See SQL Server Books Online For more information about the wildcard character. Next, we use two examples to see how patindex and select are combined.
Suppose you want to find all the records in the Description field in the Northwind.dbo.Categories table that contain the word "Bread" or "Bread", then the selection statement might be:
SELECT Description from Northwind.dbo.Categories
WHERE patindex ("%[b,b]read%", description) > 0
Here I use wildcard characters to determine uppercase and lowercase "b". After I executed this script in the Notthwind database, I got the following result:
Description
--------------------------------------------------------
Desserts, candies, and sweet breads
Breads, crackers, pasta, and cereal
This is an example of using another additional wildcard character to find some records. This example is how to select the result of the query above, the second letter of the Description field is not a 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 a conditional statement, we can filter out the record "dessert, candies, and sweet breads". The query results above have only one record.
Description
--------------------------------------------------------
Breads, crackers, pasta, and cereal
Summarize
You can now see the difference between charindex and PATINDEX search strings. The PATINDEX function supports the use of wildcard characters and can be used in a number of changing lookups. and charindex not to be. Depending on your situation, these two functions are useful for searching, controlling, and analyzing strings in SQL Server.
