Another Implementation Method for SQL like clauses (faster than like), like clauses

Another Implementation Method for SQL like clauses (faster than like), like clauses

Generally, when fuzzy search is used, you will think of LIKE

Select * from table where a like '% character %'
If an SQL statement uses multiple like fuzzy queries and the number of records is large, the query speed will be slow.
The following two methods can also implement fuzzy search:

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

The two methods are tested to be faster than the LIKE method.

I. [SQL] 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 100 ')
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 %', 'abc ')

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 SQL Server books online