Talyor is focusing on Application and Database News, are you?

Wednesday, July 20, 2005

DB \ SQL --- Return Nothing when Stored Procedure using LIKE and Wildcard

Quoted from Helps of SQL Server Books Online

Remarks
When you perform string comparisons with LIKE, all characters in the pattern string are significant, including leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by a single space), a row in which the value of that column is abc (abc without a space) is not returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero or more trailing blanks are returned.

A string comparison using a pattern containing char and varchar data may not pass a LIKE comparison because of how the data is stored. It is important to understand the storage for each data type and where a LIKE comparison may fail. The following example passes a local char variable to a stored procedure and then uses pattern matching to find all of the books by a certain author. In this procedure, the author's last name is passed as a variable.

CREATE PROCEDURE find_books @AU_LNAME char(20)
AS
SELECT @AU_LNAME = RTRIM(@AU_LNAME) + '%'
SELECT t.title_id, t.title
FROM authors a, titleauthor ta, titles t
WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id
AND a.au_lname LIKE @AU_LNAME

In the find_books procedure, no rows are returned because the char variable (@AU_LNAME) contains trailing blanks whenever the name contains fewer than 20 characters. Because the au_lname column is varchar, there are no trailing blanks. This procedure fails because the trailing blanks are significant.

However, this example succeeds because trailing blanks are not added to a varchar variable:

USE pubs
GO
CREATE PROCEDURE find_books2 @au_lname varchar(20)
AS
SELECT t.title_id, t.title
FROM authors a, titleauthor ta, titles t
WHERE 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 with LIKE
It is recommended that LIKE be used when you search for datetime values, because datetime entries can contain a variety of dateparts. For example, if you insert the value 19981231 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. A match is found, however, by the clause WHERE arrival_time LIKE '%9:20%'.

LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression, pattern, and escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any of the arguments are of Unicode data type, all arguments are converted to Unicode and Unicode pattern matching is performed. When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant. Unicode LIKE is compatible with the SQL-92 standard. ASCII LIKE is compatible with earlier versions of SQL Server.

Here is a series of examples that show the differences in rows returned between ASCII and Unicode LIKE pattern matching:

-- ASCII pattern matching with char column
CREATE 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 column
CREATE TABLE t (col1 nchar(30))
INSERT INTO t VALUES ('Robert King')
SELECT *
FROM t
WHERE col1 LIKE '% King' -- no rows returned

-- Unicode pattern matching with nchar column and RTRIM
CREATE TABLE t (col1 nchar (30))
INSERT INTO t VALUES ('Robert King')
SELECT *
FROM t
WHERE RTRIM(col1) LIKE '% King' -- returns 1 row



Note When you perform string comparisons with LIKE, all characters in the pattern string are significant, including every leading or trailing blank (space).



Web Site Counter