SQL Server Magazine

Suppose that you want to find all authors whose last names begin with B. This query would use the % symbol, to mean "any characters":

SELECT au_fname + ' ' +
   au_lname 
FROM authors
WHERE au_lname LIKE 'B%'

If you choose a case-insensitive sort order, your queries are case-insensitive. Thus, the above query would work if you use:

WHERE au_lname LIKE  'b%'

If you try this query with WHERE au_lname LIKE 'd%', you'll see that the results include two authors whose names begin with uppercase letters and one whose last name, del Castillo, begins with a lowercase letter. The query doesn't differentiate between these names. In fact, if you use a case-insensitive sort order, you need to use a trick to find authors whose names begin with lowercase letters. If you want a list of authors whose names begin with A through M, for example, you specify the range as:

SELECT au_fname + ' ' + au_lname 
FROM authors
WHERE au_lname LIKE  '[A-M]%'

Include a dash in the square brackets because LIKE '[AM]%' gives you names that begin with either A or M. You need to use square brackets, or the query will return names that begin with the three-character string 'A-M,' of which there are few.

To find names begining with Mc, modify the query to

SELECT au_fname + ' ' + au_lname 
FROM authors
WHERE au_lname LIKE  'Mc%'

If you want to exclude the author McBadden, and anyone else whose name begins with Mc, you can use LIKE 'M[^c]%'. The caret symbol excludes the following character. In this case, you are asking for names that begin with M but don't have a c as the second character. Be careful to use square brackets only around the character you're excluding. LIKE '[M^c]%' would return names that begin with M, the caret character, or C.

Because the index on the au_lname column is sorted alphabetically, you can use the index to quickly search for a name that begins with a certain letter or range of letters. But if you want a list of last names that end with "son," such as Anderson or Carson, you use the WHERE clause

WHERE au_lname LIKE  '%son'

and you can't use an index. SQL Server must scan the entire table for a match. An index search would be faster than a table scan for just a few names, but you can't avoid the table scan in this case.

To combine columns that have character and non-character data, you need to convert all the data to character data. For example, to find the price for a book, and output it as a complete sentence, you can use the query in Screen 3. This example contains two CONVERT functions. The first function converts the overly long title column from a variable column of up to 80 characters to a fixed 30 characters for display purposes. The second function converts the price, which is a money data type, to character mode.

SQL Server gives you two options for handling apostrophes and quotes in character data. One option is to repeat the single quote. When SQL Server sees two single quotes together, it assumes that you intend it to recognize the second one. For example, suppose you want to find the authors whose names begin with O'. The query in Screen 4 will show that Michael O'Leary is the only author whose name satisfies the WHERE clause in the query. SQL Server prefers single quotes. But you can mix single and double quotes to write the same example.

SELECT au_fname + ' ' + au_lname 
FROM authors
WHERE au_lname LIKE  "O'%"

The double quotes delimit the text string, and the single quote is treated as part of the text.

End of Article

Prev. page     1 2 [3]     next page -->