Like "Go" in SQL Server

Like turn from http://blog.csdn.net/wei0527/article/details/4086131

Determines whether the given string matches the specified pattern. A pattern can contain regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the string. However, you can use any fragment of a string to match a wildcard character. Wildcard characters are used to make the LIKE operator more flexible than using the = and! = string comparison operators. If any of the parameters are not part of the string data type, Microsoft®sql Server™ converts it to a string data type, if possible.

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 characters	Description	Example
%	An arbitrary string containing 0 or more characters.	The WHERE title like '%computer% ' will find all titles that contain the word computer anywhere in the title.
_ (Underline)	Any single character.	WHERE au_fname like ' _ean ' will look for all 4-letter names (Dean, Sean, etc.) ending in EAN.
[ ]	Any single character in the specified range ([a-f]) or collection ([abcdef]).	WHERE au_lname like ' [C-p]arsen ' will find the last name of the author ending with Arsen and starting with any single character between C and P, for example, Carsen, Larsen, Karsen, and so on.
[^]	Any single character that does not belong to the specified range ([a-f]) or collection ([abcdef]).	WHERE au_lname like ' de[^l]% ' will look for the last names of all authors that begin with the de and whose letters are not followed by L.

Escape_character

Any valid SQL Server expression for all data types in the String data type classification. Escape_character does not have a default value and must contain only one character.

Result type

Boolean

Result values

If match_expression matches the specified pattern, like will return TRUE.

Comments

When you use like for string comparisons, all characters in the pattern string are meaningful, including start or trailing spaces. If the comparison in the query returns all rows that contain "ABC" (with a space after ABC), the row containing the column "ABC" (with no spaces after ABC) will not be returned. However, you can ignore trailing spaces in the expression that the pattern is to match. If the comparison in the query returns all rows that contain "ABC" (with no spaces after ABC), all rows that start with "ABC" and have 0 or more trailing spaces are returned.

Because of the way data is stored, string comparisons that use a pattern containing char and varchar data may not be comparable by like. It is important to understand how each data type is stored and what causes the like comparison to fail. The following example passes a local char variable to a stored procedure and then uses pattern matching to find all the works of an author. During this process, the author's last name is passed as a variable.

CREATE PROCEDURE find_books @AU_LNAME char (asselect) @AU_LNAME = RTRIM (@AU_LNAME) + '% ' SELECT t.title_id, T.title from a Uthors 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, thechar variable (@au_lname) contains trailing spaces, which causes no rows to be returned in the find_books process. Because au_lname is listed as a varchar type, there are no trailing spaces. Because trailing spaces make sense, this process fails.

However, the following example is successful 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, t Itles 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 ‘

Here 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)

Pattern matching using like

When searching for datetime values, it is recommended to use like, because datetime items 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 will not find an exact match for the 9:20 string because the SQ L Server converts it to January 1, 1900 9:20. However, the clause WHERE arrival_time like '%9:20% ' will find a match.

Like supports ASCII pattern matching and Unicode pattern matching. ASCII pattern matching is performed when all parameters, including match_expression,pattern , and escape_character(if any) are ASCII character data types. If any of these parameters are of the Unicode data type, all parameters are converted to Unicode and Unicode pattern matching is performed. Trailing spaces make sense when you use like for Unicode data (nchar or nvarchar data types). But for non-Unicode data, trailing spaces have no meaning. Unicode like is compatible with the SQL-92 standard. The ASCII like is compatible with earlier versions of SQL Server.

The following series of examples show the difference between the ASCII like pattern match and the rows returned by the Unicode similar pattern match:

--ASCII pattern matching with char columncreate TABLE t (col1 char (+)) 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 ma Tching with nchar column and Rtrimcreate TABLE t (col1 nchar (+)) INSERT into T VALUES (' Robert King ') SELECT * from T wher E RTRIM (col1) like '% King '   --returns 1 row

Description If you use like for string comparisons, all characters in the pattern string are meaningful, including the starting or trailing spaces.

Use a% wildcard character

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

For example, this query will display 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 change depending on the version. It is recommended that you use information schema views or applicable stored procedures to process SQL Server system tables.

To see all objects of a non-system table, use the not-like ' sys% '. If there are 32 objects and like finds 13 names that match the pattern, not-like will find 19 objects that do not match the like pattern.

Using the like ' [^s][^y][^s]% ' mode does not necessarily mean the same name each time it is found. You may get only 14 names (not 19), except for the system table name, all names starting with s or the second letter Y or the third letter S will be eliminated from the result. This is because matching a string with a reverse wildcard is calculated in steps, one wildcard at a time. If any link fails during the calculation process, it is eliminated.

Use wildcard characters as literals

You can use a wildcard pattern-matching string as a literal string by placing a wildcard character in parentheses. The following table shows examples of using the LIKE keyword and [] wildcard characters.

symbol
Like ' 5[%] '	5%
like ' [_]n '	_n
like ' [a-cdf] '	a, B, C, D, or F
like ' [-acdf] '	-, A, C, D or F
like" [[] '	[
like '] ' /td>
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 table in thecustomers database may store a percent sign (%) The discount value. To search for a percent symbol as a character instead of a wildcard, you must provide the Escape keyword and escape character. For example, a sample database contains a column named comment with text 30%. To search any row in the Comment column that contains the string 30%, specify the WHERE clause consisting of the where comment like '%30!%% ' ESCAPE '! '. If you do not specify escape and escape characters, SQL Server returns all rows that contain string 30.

The following example shows how to search the notes column of the pubs database titles table for the string "50% off when the copies is purchased":

Use Pubsgoselect notesfrom titleswhere notes like ' 50%% off when the or more copies is purchased '    ESCAPE '% ' GO

Example A. Use a like with a% wildcard

The following example finds all phone numbers in the authors table that have an area code of 415.

Use Pubsgoselect phonefrom authorswhere phone like ' 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. Using a not-like with a% wildcard

The following example finds all the phone numbers in the authors table that are not 415.

Use Pubsgoselect phonefrom authorswhere phone don't like ' 415% ' ORDER by Au_lnamego

Here is the result set:

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 (row (s) affected)

C. Using the ESCAPE clause

The following example uses the escape clause and the escape character to find the exact string 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 V Alues (' Discount is off ') INSERT mytbl2 VALUES (' Discount are. 10-.15 off ') Goselect C1 from Mytbl2where C1 like '%10- 15!% off% ' ESCAPE '! ' GO

D. Using [] wildcard characters

The following example finds 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, au_fname ASCGO

The following example finds the line where the author of the last name is Carson, Carsen, Karson, or Karsen.

Use Pubsgoselect au_lname, au_fname, Phonefrom authorswhere au_lname like ' [Ck]ars[eo]n ' ORDER by au_lname ASC, au_fname as CGO

Like "Go" in SQL Server