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

In this issue, the editor will bring you about how to implement the SQL like clause. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Generally speaking, when you use fuzzy queries, everyone will think of LIKE.

Select * from table where a like'% character%'

If multiple like fuzzy queries are used in a SQL statement and the number of records is large, the speed must be very slow.

Fuzzy queries can also be implemented in the following two ways:

Select * from table where patindex ('% character%', a) > 0

Select * from table where charindex ('character', a) > 0

It has been tested that these two methods are faster than LIKE.

1. [SQL] patindex explains [Z] PATINDEX in detail

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. 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, e ­xpression is a string data type category.

Return type

Int

Annotation

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 that all records in the Description field in the Northwind.dbo.Categories table contain the words "Bread" or "bread", and the selection statement might look like this:

Select Description from Northwind.dbo.Categories

Where patindex ('% [bmaeb] read%',description) > 0

Wildcards can be used in PATINDEX to determine upper and lowercase "b"

Example 2:

Find out if the Description field in the Northwind.dbo.Categories table contains the words "Bread" or "bread" and that the second subletter is not "e".

Select Description from Northwind.dbo.Categories

Where patindex ('% [bmeme] read%',description) > 0 and patindex ('_ [^ e]%', description) = 1

By adding a PATINDEX function that uses the ^ wildcard to the conditional statement, we can filter out the record "Dessert, candies, and sweet breads". There is only one record for the above query result.

PATINDEX and CHARINDEX

The PATINDEX function supports 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.

II. Detailed explanation of SQL Server CHARINDEX and PATINDEX

If you have written a lot of programs, you may occasionally come across determining 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, we analyze the 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 method of calling the CHARINDEX function is as follows:

CHARINDEX (expression1, expression2 [, start_location])

Expression1 is the character you want to look for in expression2, and start_location is 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 example, the CHARINDEX function will return the position 11 of "S" in "Microsoft SQL Server".

Next, let's look at the CHARINDEX command:

CHARINDEX ('7.0mm,' 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 rows 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 position 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

Here is the result of the output of this command.

Last Name

-

Anders

Trujillo

Moreno

Hardy

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 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. Count the number of records in the Addresses field in the Northwind.dbo.Customer table that contain the word Road or its abbreviation Rd, and 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

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 wildcards in search strings, which 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 for, 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. If there is such a PATINDEX function:

PATINDEX ('% BC%','ABCD')

The PATINDEX function returns a result of 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 be written like this:

PATINDEX ('AB%','ABCD')

The result of the execution 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, and any numbers, the PARINDEX function command might look like this:

PATINDEX ('% [A Magi Zreco 0-9]% [A recollection Z recital 0-9]% [A Magi Z recital 0-9]%', 'XYZABC123')

Notice that a lot of commas are used in the search characters in 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 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 ('% [bmaeb] read%',description) > 0

Here I use wildcards to determine upper 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 an example of using another 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 ('% [bmaeb] read%',description) > 0

And patindex ('_ [^ e]%', description) = 1

By adding a PATINDEX function that uses the ^ wildcard to the conditional statement, we can filter out the record "Dessert, candies, and sweet breads". There is only one record for the above query result.

Description

Breads, crackers, pasta, and cereal

The above is the editor for you to share how to achieve the SQL like clause, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.