SQL Server Charindex and Patindex Detailed
If you have written a lot of programs, you may occasionally touch to determine characters or characters, whether in this article, in this article, I will discuss using Charindex and Patindex functions to search for text columns and strings. I will tell you how the two functions work, explain their difference. Also provide some examples, through these examples, you can consider using these two functions to solve a number of different character search issues. Charindex and Patindex functions are often used to search for characters or strings in a paragraph. If the characters you are searching include the characters to search, these two functions returns a non-zero integer, which is the number of characters to search in the searched character. The PATINDEX function supports search using wildcards, but Charindex does not support pacing. Next, we analyze these two functions one by one.
How to use a charIndex function
The Charindex function returns the starting position of the character or string in another string. The charIndex function call method is as follows:
Charindex (Expression1, Expression2 [, start_location])
Expression1 is the character you want to find in Expression2, start_location is the CHARINDEX function starts to find Expression1 in Expression2.
The Charindex function returns an integer, and the returned integer is the location where the string to be found is in the string of strings found. If CHARINDEX does not find the string you want to find, the function integer "0". Let's take a look at the results of the function command below:
Charindex ('SQL', 'Microsoft SQL Server')
This function command returns the starting position of "SQL" in "Microsoft SQL Server", in which case the Charindex function returns "S" in the "Microsoft SQL Server" position 11. Next, let's see this charIndex command:
Charindex ('7.0', 'Microsoft SQL Server 2000')
In this example, Charindex returns zero because strings "7.0" cannot be found in "Microsoft SQL Server". Next, in two examples to see how to use the charIndex function to solve the actual T-SQL problem.
The first example, suppose you want to display the Northwind Database Customer Table 5 lines of Last Name. This is the top 5 lines of data contactname ------------------------------ Maria Anders Ana Trujillo Antonio Moreno Thomas Hardy Christina Berglund
You can see that CustomName contains the customer's first name and last name, which are spaced apart from a space. I use the charindx function to determine the position of the middle spaces in the middle of the name. With this method, we can analyze the space position of the ContactName column so that we can display only the Last Name section of this column. This is the record of 5 lines of Last Name in front of the Customer Table of 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 found the space between first name and last name, so the substring function can separate the ContactName column, which only Last Name is elected. I add 1 to the integer returned in the Charindex function, so that last name is not from the space.
In the second example, that is, if you want to calculate the record, a field contains all the records of the specific character. The Charindex function can be easily solved your problem. Calculate the number of words ROAD or its abbreviation RD in the NorthWind.dbo.customer table, select the statement like this:
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 a character or string in another string or an initial position in the expression, the PatIndex function supports the search string using wildcard, which makes the PatIndex function value for changing search strings. The command of the Patindex function is as follows:
Patindex ('% Pattern%', Expression)
Pattern is the string you want to search, which is the string of the searched. In general, Expression is a field in a table. You need to use "%" tag before and after the Pattern, unless you searched the string of strings in the contracted string or the end.
Like the charIndex function, the Patindex function returns the starting position of the search string in the String string. If there is such a PatIndex function:
Patindex ('% BC%', 'ABCD')
The result of this Patindex function returns 2, which is the same as the charIndex function. The% tag here tells the Patindex function to find a string "BC", how many characters are available in the "BC" in the string of the search! If you want to know if you are searching in a string from a specific string, you can save the previous% tag. The PATINDED function must write this:
Patindex ('Ab%', 'Abcd')
The result of this command is returned to 1, indicating that the search string "ab" is found in the "ABCD" in the searched string.
Use wildcards to edit more simple search strings than I have more simple examples. If you want to determine if a string contains letters A and Z, there are any numbers, this PARINDEX function command may be like this:
Patindex ('% [A, Z, 0-9]% [A, Z, 0-9]% [A, Z, 0-9]%', 'XYZABC123')
Note that the search character portion in this example uses a lot of sputum. The SQL Server Book can get more information about Tong Pu. Next, we use two examples to see how Patindex and Select join us. Suppose you want to find all the records containing words "Bread" or "Bread" in the NorthWind.dbo.categories table, then select the statement to be like this: select description from northwind.dbo.categories where Patindex (' % [B, B] Read% ', Description> 0
Here I use wildcards to determine the "B" of uppercase and lowercase. After I execute this script in the NOTTHWIND database, I get the following results: Description ---------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- Desserts, Candies, And Sweet Breads Breads, Crackers, Pasta, And Cereal
This is another example of another additional wildcard to find some records. This example is how to select the above query results, the second sub-alphabet 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 a wildcard in the conditional statement, we can filter out the record of "Dessert, Candies, And Sweet Breads". The result of the above query is only one record. Description ------------------------------------------------- ------- Breads, Crackers, Pasta, And Cereal
to sum up
You can now find the difference between Charindex and Patindex search strings. The PatIndex function supports using wildcards, which can be used in many variable lookups. CHARINDEX is not available. Based on your own different situations, these two functions are very helpful to search, control, and analysis of your string in SQL Server.
