Home

	Expand Search

Transact-SQL User's Guide

Chapter 2 Queries: Selecting Data from a Table

Pattern matching

Pattern matching

You can include wildcard characters in the where clause to search for unknown characters or to group data according to common features. The sections below describe pattern matching using SQL and Transact-SQL. For more information on pattern matching, see the Reference Manual.

Matching character strings: like

The like keyword searches for a character string that matches a pattern. like is used with char, varchar, nchar, nvarchar, unichar, univarchar binary, varbinary, text, and datetime data.

The syntax for like is:

{where | having} [not] 
     column_name [not] like  "match_string" 

match_string can include the symbols in Table 2-4:

You can match the column data to constants, variables, or other columns that contain the wildcard characters shown in Table 2-4. When using constants, enclose the match strings and character strings in quotation marks. For example, using like with the data in the authors table:

• like "Mc%" searches for every name that begins with ''Mc'' (McBadden).

• like "%inger" searches for every name that ends with ''inger'' (Ringer, Stringer).

• like "%en%" searches for every name containing ''en'' (Bennet, Green, McBadden).

• like "_heryl" searches for every six-letter name ending with ''heryl'' (Cheryl).

• like "[CK]ars[eo]n" searches for ''Carsen,'' ''Karsen,'' ''Carson,'' and ''Karson'' (Carson).

• like "[M-Z]inger" searches for all names ending with ''inger'' that begin with any single letter from M to Z (Ringer).

• like "M[^c]%" searches for all names beginning with ''M'' that do not have ''c'' as the second letter.

This query finds all the phone numbers in the authors table that have an area code of 415:

select phone 
from authors 
where phone like "415%"

The only where condition you can use on text columns is like. This query finds all the rows in the blurbs table where the copy column includes the word "computer":

select * from blurbs 
where copy like "%computer%"

Adaptive Server interprets wildcard characters used without like as literals rather than as a pattern; they represent exactly their own values. The following query attempts to find any phone numbers that consist of the four characters "415%" only. It does not find phone numbers that start with 415.

select phone 
from authors 
where phone = "415%"

When you use like with datetime values, Adaptive Server converts the dates to the standard datetime format, and then to varchar or univarchar. Since the standard storage format does not include seconds or milliseconds, you cannot search for seconds or milliseconds with like and a pattern.

It is a good idea to use like when you search for datetime values, since datetime entries may contain a variety of date parts. For example, if you insert the value "9:20" and the current date into a column named arrival_time, this query will not find the value, because Adaptive Server converts the entry into "Jan 1 1900 9:20AM":

where arrival_time = "9:20"

However, the clause below finds the 9:20 value:

where arrival_time like "%9:20%"

You can use the same wildcard characters with not like, that you can use with like. For example, to find all the phone numbers in the authors table that do not have 415 as the area code, you can use either of these queries:

select phone 
from authors 
where phone not like "415%"

select phone 
from authors 
where not phone like "415%"

You cannot always duplicate not like patterns with like and the negative wildcard character [^]. Match strings with negative wildcard characters are evaluated in steps, one character at a time. If the match fails at any point in the evaluation, it is eliminated.

For example, this query finds the system tables in a database whose names begin with "sys":

select name 
from sysobjects 
where name like "sys%" 

If you have a total of 32 objects and like finds 13 names that match the pattern, not like will find the 19 objects that do not match the pattern.

where name not like "sys%"

A pattern such as the following may not produce the same results:

like [^s][^y][^s]%

Instead of 19, you might get only 14, with all the names that begin with "s" or have "y" as the second letter or have "s" as the third letter eliminated from the results, as well as the system table names.

You can search for wildcard characters by escaping them and searching for them as literals. There are two ways to use the wildcard characters as literals in a like match string: square brackets and the escape clause. The match string can also be a variable or a value in a table that contains a wildcard character.

Square brackets (Transact-SQL extension)

Use square brackets for the percent sign, the underscore, and right and left brackets. To search for a dash, rather than using it to specify a range, use the dash as the first character inside a set of brackets.

escape clause (SQL-compliant)

Use the escape clause to specify an escape character in the like clause. An escape character must be a single character string. Any character in the server's default character set can be used.

An escape character is valid only within its like clause and has no effect on other like clauses in the same statement.

The only characters that are valid following an escape character are the wildcard characters ( _ , % , [ , ] , and [^] ), and the escape character itself. The escape character affects only the character following it. If a pattern contains two literal occurrences of a character that happens to be an escape character, the string must contain four consecutive escape characters (see the last example in Table 2-6). Otherwise, Adaptive Server raises a SQLSTATE error condition and returns an error message.

Specifying more than one escape character raises a SQLSTATE error condition, and Adaptive Server returns an error message:

like "%XX_%" escape "XX"
like "%XX%X_%" escape "XX"

An escape character retains its special meaning within square brackets, unlike the wildcard characters. Do not use existing wildcard characters as escape characters in the escape clause, for these reasons:

• If you specify "_" or "%" as an escape character, it loses its special meaning within that like clause and acts only as an escape character.

• If you specify "["or "]" as an escape character, the Transact-SQL meaning of the bracket is disabled within that like clause.

• If you specify "-" or "^" as an escape character, it loses the special meaning that it normally has within square brackets and acts only as an escape character.

Adaptive Server truncates trailing blanks following "%" in a like clause to a single trailing blank. like ''% '' (percent sign followed by 2 spaces) matches ''X '' (one space); ''X '' (two spaces); ''X '' (three spaces), or any number of trailing spaces.

You can use wildcard characters for columns and column names. You might want to create a table called special_discounts in the pubs2 database to run a price projection for a special sale:

create table special_discounts
id_type char(3), discount int)
insert into special_discounts 
values("BU%", 10)

...

The table should contain the following data:

id_type discount     
------- -----------  
BU%              10  
PS%              12  
MC%              15 

The following query uses wildcard characters in id_type in the where clause:

select title_id, discount, price, price - (price*discount/100) 
from special_discounts, titles 
where title_id like id_type 

Here are the results of that query:

 title_id   discount      price                                              
 -------- ----------- -------------- --------------  
 BU1032            10          19.99          17.99  
 BU1111            10          11.95          10.76  
 BU2075            10           2.99           2.69  
 BU7832            10          19.99          17.99  
 PS1372            12          21.59          19.00  
 PS2091            12          10.95           9.64  
 PS2106            12           7.00           6.16  
 PS3333            12          19.99          17.59  
 PS7777            12           7.99           7.03  
 MC2222            15          19.99          16.99  
 MC3021            15           2.99           2.54  
 MC3026            15           NULL           NULL  
 
(12 rows affected) 

This permits sophisticated pattern matching without having to construct a series of or clauses.

Character strings and quotation marks

When you enter or search for character and date data (char, nchar, unichar, varchar, nvarchar, univarchar, datetime, and smalldatetime datatypes), you must enclose it in single or double quotation marks.

See Chapter 1, "SQL Building Blocks" for more information on character data and Chapter 6, "Using and Creating Datatypes" for more information on datetime datatypes.

"Unknown" values: NULL

A NULL in a column means no entry has been made in that column. A data value for the column is "unknown" or "not available."

NULL is not synonymous with "zero" or "blank". Rather, null values allow you to distinguish between a deliberate entry of zero for numeric columns (or blank for character columns) and a non-entry, which is NULL for both numeric and character columns.

NULL can be entered in a column where null values are permitted in two ways:

• If you do not enter any data, Adaptive Server automatically enters "NULL".

• Users can explicitly enter the word "NULL" or "null" without quotation marks.

If you type the word ''NULL'' in a character column and include quotation marks, it is treated as data, rather than a null value.

Query results display the word NULL. For example, the advance column of the titles table allows null values. By inspecting the data in that column you can tell whether a book had no advance payment by agreement (the row MC2222 has zero in the advance column) or whether the advance amount was not known when the data was entered (the row MC3026 has NULL in the advance column).

select title_id, type, advance 
from titles 
where pub_id = "0877" 

title_id     type             advance 
--------     ----------       --------- 
MC2222       mod_cook              0.00 
MC3021       mod_cook         15,000.00 
MC3026       UNDECIDED             NULL 
PS1372       psychology        7,000.00 
TC3218       trad_cook         7,000.00 
TC4203       trad_cook         4,000.00 
TC7777       trad_cook         8,000.00 
 
(7 rows affected)  

Use is null in where, if, and while clauses (discussed in Chapter 13, "Using Batches and Control-of-Flow Language") to compare column values to NULL and to select them or perform a particular action based on the results of the comparison. Only columns that return a value of TRUE are selected or result in the specified action; those that return FALSE or UNKNOWN do not.

The following example selects only rows for which advance is less than $5000 or NULL:

select title_id, advance 
from titles 
where advance < $5000 or advance is null

Adaptive Server treats null values in different ways, depending on the operators that you use and the type of values you are comparing. In general, the result of comparing null values is UNKNOWN, since it is impossible to determine whether NULL is equal (or not equal) to a given value or to another NULL. The following cases return TRUE when expression is any column, variable or literal, or combination of these, which evaluates as NULL:

• expression is null

• expression = null

• expression = @x where @x is a variable or parameter containing NULL. This exception facilitates writing stored procedures with null default parameters.

• expression != n where n is a literal not containing NULL and expression evaluates to NULL.

The negative versions of these expressions return TRUE when the expression does not evaluate to NULL:

• expression is not null

• expression != null

• expression != @x

When the keywords like and not like are used instead of the operators = and !=, the opposite occurs. This comparison returns TRUE:

• expression not like null

While this comparison returns FALSE:

• expression like null

Note that the far right side of these expressions is a literal null, or a variable or parameter containing NULL. If the far right side of the comparison is an expression (such as @nullvar + 1), the entire expression evaluates to NULL.

Null column values do not join with other null column values. Comparing null column values to other null column values in a where clause always returns UNKNOWN, regardless of the comparison operator, and the rows are not included in the results. For example, this query returns no result rows where column1 contains NULL in both tables (although it may return other rows):

select column1 
from table1, table2 
where table1.column1 = table2.column1

These operators return results when used with a NULL:

• = returns all rows that contain NULL.

• != or <> returns all rows that do not contain NULL.

When set ansinull is "on" for SQL compliance, the = and != operators do not return results when used with a NULL. Regardless of the set ansinull option value, the following operators never return values when used with a NULL: <, <=, !<, >, >=, !>.

Adaptive Server can determine that a column value is NULL. Thus, this will be considered true:

column1 =  NULL

However, the following comparisons can never be determined, since NULL means "having an unknown value":

where column1 > null

There is no reason to assume that two unknown values are the same.

This logic also applies when you use two column names in a where clause, that is, when you join two tables. A clause like "where column1 = column2" does not return rows where the columns contain null values.

You can also find null values or non-null values with this pattern:

where column_name is [not] null 

For example:

where advance < $5000 or advance is null 

Some of the rows in the titles table contain incomplete data. For example, a book called The Psychology of Computer Cooking (title_id = MC3026) has been proposed and its title, title identification number, and probable publisher have been entered. However, since the author has no contract yet and details are still up in the air, null values appear in the price, advance, royalty, total_sales, and notes columns. Because null values do not match anything in a comparison, a query for all the title identification numbers and advances for books with advances of less than $5000 will not find The Psychology of Computer Cooking.

select title_id, advance 
from titles 
where advance < $5000 

title_id  advance 
--------  ---------- 
MC2222         0.00 
PS2091     2,275.00 
PS3333     2,000.00 
PS7777     4,000.00 
TC4203     4,000.00 
 
(5 rows affected) 

Here is a query for books with an advance of less than $5000 or a null value in the advance column:

select title_id, advance 
from titles 
where advance < $5000 
  or advance is null 

title_id  advance 
--------  ---------- 
MC2222          0.00 
MC3026          NULL 
PC9999          NULL 
PS2091      2,275.00 
PS3333      2,000.00 
PS7777      4,000.00 
TC4203      4,000.00 
 
(7 rows affected) 

See Chapter 7, "Creating Databases and Tables," for information on NULL in the create table statement and for information on the relationship between NULL and defaults. See Chapter 8, "Adding, Changing, and Deleting Data," for information on inserting null values into a table.

There is an important logical difference between FALSE and UNKNOWN: the opposite of false ("not false") is true, while the opposite of UNKNOWN is still UNKNOWN. For example, "1 = 2" evaluates to false and its opposite, "1 != 2", evaluates to true. But "not unknown" is still unknown. If null values are included in a comparison, you cannot negate the expression to get the opposite set of rows or the opposite truth value.

Use the isnull built-in function to substitute a particular value for nulls. The substitution is made only for display purposes; actual column values are not affected. The syntax is:

isnull(expression, value) 

For example, use the following statement to select all the rows from titles, and display all the null values in column notes with the value unknown.

select isnull(notes, "unknown") 
from titles

An expression with an arithmetic or bitwise operator evaluates to NULL if any of the operands is null. For example, this evaluates to NULL if column1 is NULL:

1 + column1

If you concatenate a string and NULL, the expression evaluates to the string. For example:

select "abc" + NULL + "def"

-----
abcdef 

In Transact-SQL, system-generated NULLs, such as those that result from a system function like convert, behave differently than user-assigned NULLs. For example, in the following statement, a not equals comparison of the user-provided NULL and 1 returns TRUE:

if (1 != NULL) print "yes" else print "no"

yes

The same comparison with a system-generated NULL returns UNKNOWN:

if (1 != convert(integer, NULL))
print "yes" else print "no"

no

For more consistent behavior, set ansinull on. Then both system-generated and user-provided NULLs cause the comparison to return UNKNOWN.

Connecting conditions with logical operators

The logical operators and, or, and not are used to connect search conditions in where clauses. The syntax is:

{where | having} [not] 
     column_name join_operator column_name 

where join_operator is a comparison operator and column_name is the column used in the comparison. Qualify the name of the column if there is any ambiguity.

and joins two or more conditions and returns results only when all of the conditions are true. For example, the following query finds only the rows in which the author's last name is Ringer and the author's first name is Anne. It does not find the row for Albert Ringer.

select * 
from authors 
where au_lname = "Ringer" and au_fname = "Anne" 

or also connects two or more conditions, but it returns results when any of the conditions is true. The following query searches for rows containing Anne or Ann in the au_fname column.

select * 
from authors 
where au_fname = "Anne" or au_fname = "Ann" 

You can specify as many as 252 and and or conditions.

not negates the expression that follows it. The following query selects all the authors who do not live in California:

select * from authors 
where not state = "CA" 

When more than one logical operator is used in a statement, and operators are normally evaluated before or operators. You can change the order of execution with parentheses. For example:

select * from authors 
where (city = "Oakland" or city = "Berkeley") and state = "CA" 

Arithmetic and bitwise operators are handled before logical operators. When more than one logical operator is used in a statement, not is evaluated first, then and, and finally or. See "Bitwise operators" for more information.

For example, the following query finds all the business books in the titles table, no matter what their advances are, as well as all psychology books that have an advance of more than $5500. The advance condition pertains only to psychology books because the and is handled before the or.

select title_id, type, advance 
from titles 
where type = "business" or type = "psychology" 
  and advance > 5500

title_id  type        advance 
--------  ----------  ---------- 
BU1032    business     5,000.00 
BU1111    business     5,000.00 
BU2075    business    10,125.00 
BU7832    business     5,000.00 
PS1372    psychology   7,000.00 
PS2106    psychology   6,000.00 
 
(6 rows affected) 

You can change the meaning of the query by adding parentheses to force evaluation of the or first. This query finds all business and psychology books that have advances of more than $5500:

select title_id, type, advance 
from titles 
where (type = "business" or type = "psychology") 
  and advance > 5500 

title_id  type         advance 
--------  ----------   --------- 
BU2075    business     10,125.00 
PS1372    psychology    7,000.00 
PS2106    psychology    6,000.00 
 
(3 rows affected)