Mssqllike query character

Mssqllike query character syntax match_expression [NOT] LIKEpattern [ESCAPEescape_character] parameter match_expression a valid SQLServer expression of any string data type. The search mode in patternmatch_expression can contain the following valid SQLServer wildcard mssql like Query characters

Syntax
Match_expression [NOT] LIKE pattern [ESCAPE escape_character]

Parameters
Match_expression

A valid SQL Server expression for any string data type.

Pattern

The search mode in match_expression can contain the following valid SQL Server wildcard characters.

Wildcard Description Example
% Any string containing zero or more characters. WHERE title LIKE '% computer %' will be found in

The title of all titles containing the word computer anywhere.
_ (Underline) any single character. WHERE au_fname LIKE '_ ean' searches for

It has four letters (such as Dean and Sean ).
[] Any single character in the specified range ([a-f]) or set ([abcdef. WHERE au_lname

LIKE '[C-P] arsen' looks for any single character starting with arsen and between C and P

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

LIKE 'de [^ l] % 'searches for the last names of all authors whose names start with de and whose later letters are not l.

Escape_character

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

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

Result type
Boolean

Result Value
If match_expression matches the specified mode, LIKE returns TRUE.

Note
When using LIKE for string comparison, All characters in the pattern string are meaningful, including start or tail

Space. If you want to return all rows containing "abc" (there is a space after abc) for comparison in the query, no

Returns the row of the column containing "abc" (without spaces after abc. However, the expression to be matched by the pattern can be ignored.

Trailing space in. If you want to return all rows that contain "abc" (no space after abc) for comparison in the query

All rows starting with "abc" with zero or multiple trailing spaces are returned.

For data storage, strings containing char and varchar data modes may not exist.

The method is compared by LIKE. Understand the storage methods of each data type and the reasons for LIKE comparison failure

Important. In the following example, the local char variable is passed to the stored procedure, and a pattern matching is used to find an author.

. In this process, the author's surname is passed as a variable.

Create procedure find_books @ AU_LNAME char (20) 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 char variable when the number of characters in the name is less than 20

(@ AU_LNAME) will contain trailing spaces, which causes no rows to be returned during find_books. Because

The au_lname column is of the varchar type, so there is no trailing space. Because trailing spaces are meaningful

The process failed.

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

USE pubsGOCREATE PROCEDURE find_books2 @ au_lname varchar (20) 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 +

The following result set is '%' EXEC find_books2 'login:

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) use the LIKE pattern to match
LIKE is recommended when searching for datetime values, because datetime may contain various date sections.

For example, if you insert a value of 19981231 into the column named arrival_time, the clause WHERE

Arrival_time = will not be able to find the exact match of the string, because SQL Server converts it

For am, January 1, January 1, 1900. However, the clause WHERE arrival_time LIKE '% 9:20%'

Match will be found.

LIKE supports ASCII and Unicode matching. When all parameters, including

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

Type, the ASCII pattern matching is performed. If any of the parameters belongs to the Unicode data type, all parameters

The number is converted to Unicode and the Unicode mode is matched. When Unicode data (nchar or

Nvarchar data type) When LIKE is used, trailing spaces are meaningful. However, for non-Unicode data

, Trailing spaces are meaningless. Unicode LIKE is compatible with SQL-92 standards. Ascii like and SQL

Earlier versions of the Server are compatible.

The following examples show the rows returned by matching the ascii like pattern and the Unicode LIKE pattern.

Differences:

-- 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 pattern matching with nchar columnCREATE TABLE

T (col1 nchar (30) insert into t VALUES ('Robert king') SELECT * FROM t WHERE

Col1 LIKE '% king' -- no rows returned -- Unicode pattern matching

Nchar column and rtrimcreate table t (col1 nchar (30) insert into t VALUES

('Robert king') SELECT * FROM t where rtrim (col1) LIKE '% king' -- returns

1 row indicates that if LIKE is used for string comparison, All characters in the pattern string are meaningful.

Including start space or trailing space.

Use % wildcard
If you specify LIKE '5% ', SQL Server searches for the number 5 with zero or multiple arbitrary characters.

For example, this query will display all the system tables because they all start with the letter sys:

SELECT TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME LIKE 'sys %'

Note: The system tables can be changed with different versions. Information Architecture view or applicable storage is recommended

Process SQL Server System table.

To view all objects in a non-system table, use not like 'sys % '. If there are 32 objects in total and

If you find 13 LIKE matches the pattern, 19 not like does NOT match the LIKE pattern.

.

In the LIKE '[^ s] [^ y] [^ s] %' mode, the names may not be the same each time. Only 14 names may be obtained.

Name (instead of 19), except for the system table name, all start with s or the second letter is y or the third

The name of s is also removed from the result. This is because matching strings with reverse wildcards is step-by-step.

One wildcard character at a time for row calculation. If the matching fails in any part of the computing process, it will be eliminated.

.

Use wildcards as text
The wildcard pattern matching string can be used as a text string by placing the wildcard in parentheses. The following table shows

Example of using the LIKE keyword and the [] wildcard.

Symbol 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

Use the ESCAPE clause pattern matching
You can search for strings that contain one or more special wildcards. For example, discounts in the MERs Database

The table may store discount values with a percent sign (%. To search for a percent sign as a character rather than a wildcard, you must

Provides ESCAPE keywords and ESCAPE characters. For example, a sample database contains a column named comment.

Contains text 30%. To search for any row containing string 30% at any position in the comment column, specify

By WHERE comment LIKE '% 30! % 'Escape '! 'Where clause. If you do not specify

For ESCAPE and ESCAPE characters, SQL Server returns all rows containing string 30.

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

Or more copies are purchased ":

USE pubsGOSELECT notesFROM titlesWHERE notes LIKE '2017% off when 50% or more

Copies are purchased 'escape '%' GO example
A. Use LIKE with % wildcard
In the following example, we will find all the phone numbers in the authors table with a cell number of 415.

USE pubsGOSELECT phoneFROM authorsWHERE phone LIKE '000000' ORDER by au_lnameGO

The following is the result set:

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

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

843-2991 415 935-4228 (11 row (s) affected) B. Use NOT LIKE with % wildcard
In the following example, find all the phone numbers in the authors table that are not 415.

USE pubsGOSELECT phoneFROM authorsWHERE phone not like '000000' ORDER

The following is the result set of au_lnameGO:

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

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

826-0752 913 843-0462 408-496 (12 row (s) affected) C. Use the ESCAPE Clause
The following example uses the ESCAPE clause and ESCAPE character to find the exact string 10-15% in column c1 of mytbl2.

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. Use the [] wildcard
In the following example, find the author whose name is Cheryl or Sheryl.

USE pubsGOSELECT au_lname, au_fname, phoneFROM authorsWHERE au_fname LIKE

'[CS] heryl' order by au_lname ASC. In the example of au_fname ASCGO, the last names are Carson and Carsen.

The row where the author of Karson or Karsen is located.

USE pubsGOSELECT au_lname, au_fname, phoneFROM authorsWHERE au_lname LIKE

'[CK] ars [eo] n' order by au_lname ASC, au_fname ASCGO see
Expression

Function

SELECT

WHERE

See the Advanced Search below

Basic Condition Query

Comparison OPERATOR:>, >=, <, <=, = ,! =

Between a and B, in (a, B, c), not exists, is null, like '% _', or, and, any, all, etc.

Query records in a specific range of fields

SELECT StudentID, Score FROM SCore WHERE Score BETWEEN 60 AND 80

Query records that match the content in a field with the list of queried content.

SELECT SName AS student name, SAddress As address FROM Students WHERE SAddress IN ('

Beijing', 'guangzhou ', 'shanghai ')