Click to go to the Precision Forestry Cooperative website

Advanced Searching Capabilities

Determines whether or not a given character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string. Wildcard characters, however, can be matched with arbitrary fragments of the character string. Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators. If any of the arguments are not of character string data type, Microsoft® SQL Server™ converts them to character string data type, if possible.

Syntax

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

Arguments

match_expression

Is any valid SQL Server expression of character string data type.

pattern

Is the pattern to search for in match_expression, and can include these valid SQL Server wildcard characters.

Wildcard character	Description	Example
%	Any string of zero or more characters.	WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
_ (underscore)	Any single character.	WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).	WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.
[^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef]).	WHERE au_lname LIKE 'de[^l]%' all author last names beginning with de and where the following letter is not l.

escape_character

Is any valid SQL Server expression of any of the data types of the character string data type category. escape_character has no default and must consist of only one character.

Result Types

Boolean

Result Value

LIKE returns TRUE if the match_expression matches the specified pattern.

Using Wildcard Characters as Literals

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The table shows several examples of using the LIKE keyword and the [ ] wildcard characters.

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

Pattern Matching with the ESCAPE Clause

You can search for character strings that include one or more of the special wildcard characters. For example, the discounts table in the customers database may store discount values that include a percent sign (%). To search for the percent sign as a character instead of as a wildcard character, the ESCAPE keyword and escape character must be provided. For example, a sample database contains a column named comment that contains the text 30%. To search for any rows containing the string 30% anywhere in the comment column, specify a WHERE clause of WHERE comment LIKE '%30!%%' ESCAPE '!'. Unless ESCAPE and the escape character are specified, SQL Server returns any rows with the string 30.

School of Environmental and Forest Sciences

USDA Forest Service State & Private Forestry

The Rural Technology Home Page is provided by the College of Forest Resources. For more information, please contact the Rural Technology Initiative, University of Washington Box 352100 Seattle, WA 98195, (206) 543-0827. © 2000-2004, University of Washington, Rural Technology Initiative, including all photographs and images unless otherwise noted. To view the www.ruraltech.org privacy policy, click here.

Last Updated 2/2/2012 6:37:23 PM