Shulou(Shulou.com)05/31 Report--

What is the difference between patindex and charindex in SQLserver? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. Full match lookup string 2, fuzzy lookup string CHARINDEX, and PATINDEX functions all return the start position of the specified pattern. PATINDEX can use wildcards, while CHARINDEX cannot. Both functions take two parameters: 1 the pattern whose location you want to get. With PATINDEX, a pattern is a literal string that can contain wildcards. With CHARINDEX, the pattern is a literal string (cannot contain wildcards). 2 string value expression (usually the column name). For example, find the start position of the schema "wonderful" in a particular row of the notes column in the titles table. The copy code is as follows: USE pubs SELECT CHARINDEX ('wonderful', notes) FROM titles WHERE title_id =' TC3218'

For example, use wildcards to find the beginning of the schema "candies" in any row of the Description column in the Categories table: the copy code is as follows: USE Northwind GO SELECT CategoryID, PATINDEX ('% candies%', Description) AS POSITION FROM Categories WHERE PATINDEX ('% candies%', Description) 0

I hope it works for all of you. If you still don't understand the above, you can move on: 1. [SQL] patindex details [Z] PATINDEX returns the starting position of a pattern in the specified expression for the first time; if the pattern is not found in all valid text and character data types, it returns zero. The syntax PATINDEX ("% pattern%", e-xpression) parameter pattern a string. Wildcards can be used, but there must be% characters before and after pattern (except when searching for the first and last characters). Pattern is an expression for a category of short character data types. E-xpression an expression, usually the column in which you want to search for the specified pattern, and e-xpression is a string data type category. The return type int comment PATINDEX is useful for text data types; in addition to IS NULL, IS NOT NULL, and LIKE (these are the only other comparison operations valid for text types in the Where clause), PATINDEX can also be used in the Where clause. -- example 1: find out all records in the Northwind.dbo.Categories table that contain the words "Bread" or "bread" in the Description field Then the selection statement might look like this: in Select Description from Northwind.dbo.Categories Where patindex ("% [bMagneB] read%", description) > 0 PATINDEX, wildcards can be used to determine uppercase and lowercase "b" example 2: find out whether the Description field in the Northwind.dbo.Categories table contains the words "Bread" or "bread" and the second subletter is not "e". Select Description from Northwind.dbo.Categories where patindex ("% [bselect Description from Northwind.dbo.Categories where patindex B] read%", description) > 0 and patindex ("_ [^ e]%", description) = 1 We can filter out the record "Dessert, candies, and sweet breads" by adding a PATINDEX function that uses the ^ wildcard in the conditional statement. There is only one record for the above query result. The PATINDEX and CHARINDEX PATINDEX functions support the use of wildcards and can be used in many variable lookups. And CHARINDEX can't. Depending on your own situation, these two functions are very helpful for your search, control, and analysis of strings in SQL Server. 2. SQL Server CHARINDEX and PATINDEX detailed explanation if you have written a lot of programs, you may occasionally come across to determine whether characters or strings are included in a paragraph of text. 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 and explain the difference between them. 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 segment of characters. If the character being searched contains the character to be searched, the two functions return a non-zero integer, which is the number of digits at the beginning of the character to be searched. The PATINDEX function supports searching using wildcards, while CHARINDEX does not support wildcards. Next, let's analyze these two functions one by one. How to use the CHARINDEX function CHARINDEX function to return 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 one of the characters to be found in expression2, and start_location is the location where the CHARINDEX function starts looking for expression1 in expression2. The CHARINDEX function returns an integer that is the position of the string you are looking for in the string being found. If CHARINDEX does not find the string you are looking for, the function integer "0". Let's look at the result of the execution of the following function command: CHARINDEX ("SQL", "Microsoft SQL Server") this function command will return the starting position of "SQL" in "Microsoft SQL Server". In this case, the CHARINDEX function will return the 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". Let's take a look at how to use the CHARINDEX function to solve the actual T-SQL problem with two examples. For the first example, suppose you want to display the Last Name of the first five rows of the contact column in the Customer table of the Northwind database. This is the first five lines of data ContactName-- Maria Anders Ana Trujillo Antonio Moreno Thomas Hardy Christina Berglund. As you can see, CustomName contains the customer's First Name and Last Name, separated by a space. I use the CHARINDX function to determine the location of the space between the two names. In this way, we can analyze the space placement of the ContactName column so that we can display only the last name portion of the column. This is the record showing the first five rows of last name in the Customer table of Northwind! Select top 5 substring (ContactName,charindex (", ContactName) + 1, len (ContactName)) as [Last Name] from Northwind.dbo.customers is the result of this command output. The Last Name-- Anders Trujillo Moreno Hardy Berglund CHARINDEX function finds the space between First Name and Last Name, so the SUBSTRING function can separate the ContactName column so that only Last Name is selected. I add 1 to the integer returned by the CHARINDEX function so that Last Name does not start with a space. In the second example, if you want to calculate the number of records in a record, a field contains all records with specific characters. The CHARINDEX function can easily solve your problem. Calculate the number of records in the Addresses field of the Northwind.dbo.Customer table that contain the word Road or its abbreviation Rd. Select statements like this: SELECT count (*) from Northwind.dbo.Customers WHERE CHARINDEX ("Rd", Address) > 0 or CHARINDEX ("Road", Address) > 1 how to use the PATINDEX function PATINDEX function to return the starting position of a character or string in another string or expression. The PATINDEX function supports the use of wildcards in search strings. This makes the PATINDEX function valuable for changing search strings. The command for the PATINDEX function is as follows: PATINDEX ("% pattern%", expression) Pattern is the string you want to search, and expression is the string to be searched. In general, expression is a field in a table, and pattern needs to be marked with "%" before and after the string you are searching for, unless the string you are searching for is at the beginning or end of the contracted string. Like the CHARINDEX function, the PATINDEX function returns the starting position of the search string in the searched string. Suppose there is such a PATINDEX function: PATINDEX ("% BC%", "ABCD") the PATINDEX function returns 2, which is the same as the CHARINDEX function. The% tag here tells the PATINDEX function to find the string "BC", no matter how many characters there are before and after "BC" in the searched string! If you want to know whether the searched string starts with a specific string, you can omit the previous% tag. The PATINDED function should write: PATINDEX ("AB%", "ABCD") the result of this command returns 1, indicating that the searched string "AB" was found in the searched string "ABCD". Use wildcards to edit search strings that are much more complex than the simple example I gave above. If you want to determine whether a string contains the letters An and Z, as well as any numbers, the PARINDEX function command might look like this: PATINDEX ("% [AMagre ZJ 0-9]% [AMagi ZJ 0-9]% [A Magi ZJ 0-9]%", "XYZABC123") notice that a lot of commas are used in the search character section of the above example. Check out SQL Server Books online for more information about wildcards. Next, let's use two examples to see how PATINDEX and SELECT work together. Suppose you want to find out all the records in the Northwind.dbo.Categories table that contain the words "Bread" or "bread" in the Description field, then the select statement might look like this: SELECT Description from Northwind.dbo.Categories WHERE patindex ("% [bmenary B] read%", description) > 0 where I use wildcards to determine uppercase and lowercase "b". After I execute this script in the Notthwind database, I get the following result: Description-Desserts, candies, and sweet breads Breads, crackers, pasta, and cereal this is another example of using an extra wildcard to find some records. This example is how to select a record in the above query result where the second subletter of the Description field is not "e". Select Description from Northwind.dbo.Categories where patindex ("% [bselect Description from Northwind.dbo.Categories where patindex B] read%", description) > 0 and patindex ("_ [^ e]%", description) = 1 We can filter out the record "Dessert, candies, and sweet breads" by adding a PATINDEX function that uses the ^ wildcard in the conditional statement. There is only one record for the above query result. Description-Breads, crackers, pasta, and cereal summarize that you can now see the difference between CHARINDEX and PATINDEX searching for strings. The PATINDEX function supports the use of wildcards and can be used in many variable lookups. And CHARINDEX can't.

After reading the above, have you mastered the difference between patindex and charindex in SQLserver? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.