MSSQL like query characters

MSSQL like query characters

Grammar
Match_expression [NOT] like pattern [ESCAPE escape_character]

Parameters
Match_expression

A valid SQL Server expression for any string data type.

Pattern

The search pattern in Match_expression can contain the following valid SQL Server wildcard characters.

Wildcard Description Example
% contains any string of 0 or more characters. WHERE title like '%computer% ' will find in

All titles that contain the word computer at any place in the title.
_ (underline) any single character. WHERE au_fname like ' _ean ' will look for the end of the EAN

There are 4-letter names (Dean, Sean, etc.).
[] Any single character in the specified range ([a-f]) or collection ([abcdef]). WHERE au_lname

Like ' [C-p]arsen ' will look for any single character ending in Arsen and between C and P

The author's surname, for example, Carsen, Larsen, Karsen, etc.
[^] Any single character that does not belong to the specified range ([a-f]) or set ([abcdef]). WHERE au_lname

Like ' de[^l]% ' will look for the last names of all authors who start with de and whose letter is not L.

Escape_character

Any valid SQL Server expression for all data types in the string data type Taxonomy.

Escape_character has no default value and must contain only one character.

Result type
Boolean

Result value
If match_expression matches the specified pattern, like returns TRUE.

Comments
When using like for string comparisons, all characters in the pattern string are meaningful, including starting or trailing

Space. If the comparison in the query returns all rows that contain "ABC" (with a space after ABC), it will not

Returns the row containing the column with "ABC" (with no spaces after ABC). But you can ignore the expression that the pattern will match

The trailing spaces in the. If the comparison in the query returns all rows that contain "ABC" (with no spaces after ABC), the

All rows starting with "ABC" with 0 or more trailing spaces will be returned.

Because of the way the data is stored, strings that contain the char and varchar data patterns may be less

Method by like comparison. Understanding how each data type is stored and why a like comparison fails is very

Important. The following example passes a local char variable to a stored procedure and then uses pattern matching to find an author

of all writings. In this process, the author's last name is passed as a variable.

CREATE PROCEDURE find_books @AU_LNAME char (m) asselect @AU_LNAME = RTRIM

(@AU_LNAME) + '% ' SELECT t.title_id, T.title from authors A, titleauthor TA,

Titles Twhere a.au_id = ta.au_id and ta.title_id = t.title_id and

A.au_lname like @AU_LNAME when the name contains fewer than 20 o'clock characters, the char variable

(@AU_LNAME) will contain trailing spaces, which causes no rows to return during the find_books process. Because

Au_lname is listed as varchar type, so there are no trailing spaces. Because trailing spaces make sense, this

Process failed.

The following example is successful, however, because trailing spaces are not added to the varchar variable:

Use pubsgocreate PROCEDURE find_books2 @au_lname varchar (asselect)

t.title_id, T.title from authors A, titleauthor TA, titles twhere a.au_id =

ta.au_id and ta.title_id = t.title_id and a.au_lname like @au_lname +

'% ' EXEC find_books2 ' ring ' below is the result set:

title_id Title

-------- -----------------------------------------------------

----------MC3021 the Gourmet microwave

PS2091 is anger the enemy

PS2091 is anger the enemy

PS2106 Life without Fear

(4 row (s)

affected) using like pattern matching
It is recommended to use like when searching for datetime values, because a datetime item may contain various date parts.

For example, if you insert a value of 19981231 9:20 into a column named Arrival_time, the clause WHERE

Arrival_time = 9:20 The exact match for the 9:20 string cannot be found because SQL Server turns it

Change to January 1, 1900 9:20. However, the clause WHERE arrival_time like '%9:20% '

A match will be found.

Like supports ASCII pattern matching and Unicode pattern matching. When all parameters, including

Match_expression, pattern, and escape_character (if any) are ASCII character data classes

, an ASCII pattern match is performed. If any of the parameters belong to a Unicode data type, all the arguments

Number is converted to Unicode and a Unicode pattern match is performed. When the Unicode data (nchar or

nvarchar data type) when using like, trailing spaces make sense. But for non-Unicode data

, trailing spaces have no meaning. Unicode like is compatible with the SQL-92 standard. ASCII like and SQL

Earlier versions of Server are compatible.

The following series of examples shows the rows returned by the ASCII like pattern match with the Unicode like pattern match

The difference between:

--ASCII pattern matching with char columncreate TABLE t (col1 char (30))

INSERT into T VALUES (' Robert King ') SELECT * from T WHERE col1 like '% King '

--Returns 1 row--Unicode matching with nchar columncreate TABLE

T (col1 nchar) INSERT into T VALUES (' Robert King ') SELECT * from T WHERE

col1 like '% King '--no rows returned--Unicode matching with

NCHAR column and Rtrimcreate TABLE t (col1 nchar) INSERT into T VALUES

(' Robert King ') SELECT * from T WHERE RTRIM (col1) like '% King '-Returns

1 row description If you use like for string comparisons, all characters in the pattern string are meaningful, and the package

Enclose the starting or trailing spaces.

Using% wildcard characters
If you specify like ' 5% ', SQL Server searches for a number with 0 or more arbitrary characters followed by 5.

For example, this query displays all system tables in the database because they all start with the letter sys:

SELECT Table_namefrom information_schema. Tableswhere table_name like ' sys% '

Note: System tables can be changed with different versions. Recommended use of Information Schema View or applicable storage

Procedure processes SQL Server system tables.

To refer to all objects in a system table, use not like ' sys% '. If there are 32 objects and

Like to find 13 names that match the pattern, it will find 19 that do not match the like pattern

The object.

Using the like ' [^s][^y][^s]% ' mode does not necessarily have the same name every time you find it. Probably only got 14 names.

Called (instead of 19), all except the system table name, all starts with s or the second letter is Y or third

The names of the letters S will also be eliminated from the result. This is because matching a string with a reverse wildcard character is a step in

One wildcard character at a time, as calculated by the row. If either link fails in the calculation, it is eliminated

。

Use wildcard characters as text
You can use a wildcard pattern matching string as a literal string by placing the wildcard characters in parentheses. The following table shows

Examples of using the LIKE keyword and [] wildcard characters.

Symbolic meaning
Like ' 5[%] ' 5%
Like ' [_]n ' _n
Like ' [A-CDF] ' A, B, C, D or F
Like ' [-ACDF] '-, A, C, D, or F
Like ' [[] ' [
Like '] '
Like ' abc[_]d% ' abc_d and Abc_de
Like ' abc[def] ' abcd, ABCE and ABCF

Pattern matching using the ESCAPE clause
You can search for a string that contains one or more special wildcard characters. For example, the discounts in the Customers database

Table may be stored with percent sign (%) The discount value. To search for a percent semicolon that is a character instead of a wildcard, you must

Provides escape keys and escape characters. For example, a sample database contains a column named Comment, and the column

Contains Text 30%. To search for any row that contains string 30% anywhere in the comment column, specify

Where clause consists of where comment like '%30!%% ' ESCAPE '! '. If you do not specify

Escape and escape characters, SQL Server returns all rows containing the string 30.

The following example shows how to search for a string in the notes column of the Pubs database titles table "50% off when 100

Or more copies are purchased ":

Use Pubsgoselect notesfrom titleswhere notes like "50%% off" or more

Copies are purchased ' ESCAPE '% ' go example
A. Use like with% wildcard characters
The following example finds all phone numbers in the authors table that have an area code of 415.

Use Pubsgoselect phonefrom authorswhere phone as ' 415% ' ORDER by Au_lnamego

Here is the result set:

Phone------------415 658-9932 415 548-7723 415 836-7128 415 986-

7020 415 836-7128 415 534-9219 415 585-4620 415 354-7128 415 834-2919-415

843-2991 415 935-4228 (one row (s) affected) B. Don't like with% wildcard
The following example finds all phone numbers in the authors table that are not 415 in the area code.

Use Pubsgoselect phonefrom authorswhere phone don't like ' 415% '

Au_lnamego is the result set below:

Phone------------503 745-6402 219 547-9982 615 996-8275 615 297-

2723 707 938-6445 707 448-4982 408 286-2428 301 946-8853 801 826-0752-801

826-0752 913 843-0462 408 496-7223 (a row (s) affected) C. Use the ESCAPE clause
The following example uses the escape clause and the escape character to find the exact string 10-15% in the C1 column of the MYTBL2 table.

Use PUBSGOIF EXISTS (SELECT table_name from INFORMATION_SCHEMA. TABLES

WHERE table_name = ' mytbl2 ') DROP table mytbl2gouse pubsgocreate table

MYTBL2 (c1 sysname) Goinsert mytbl2 VALUES (' Discount is 10-15% off ') INSERT

Mytbl2 VALUES (' Discount is. 10-.15 off ') Goselect C1 from Mytbl2where C1

Like '%10-15!% off% ' ESCAPE '! ' GOD. Using [] wildcard characters
The following example looks for an author whose name is Cheryl or Sheryl.

Use Pubsgoselect au_lname, au_fname, Phonefrom authorswhere

' [Cs]heryl ' ORDER by au_lname ASC, au_fname Ascgo The following example to find the last name is Carson, Carsen

, Karson, or Karsen the author of the row.

Use Pubsgoselect au_lname, au_fname, Phonefrom authorswhere

' [ck]ars[eo]n ' ORDER by au_lname ASC, au_fname Ascgo See
An expression

Function

SELECT

WHERE

Let's look at an advanced search.

Basic condition Query

Comparison operators: >,>=,<,<=,=,!=

Between A and B,in (a,b,c), not exists,is null,like '%_ ', Or,and, Any,all, etc.

Query the contents of a field for a specific range of records

SELECT StudentID, Score from Score WHERE Score BETWEEN and 80

Query for records that match the contents of a field with the list of query contents listed

SELECT sname as trainee name, saddress as address from Students WHERE saddress in ('

Beijing ', ' Guangzhou ', ' Shanghai '