COALESCE doesn''t LIKE CHAR
-
COALESCE has always been my favorite when creating dynamic query.
Yesterday, I was shocked to find that LIKE comparisons will return NO results against COALESCE with char or nchar column, whatever so
(In my case, I am searching against char(10) PhoneNumber column.)
Please let me know if this is a known bug, or you experience the same results. Thanks!
Example: you can run the query on [Northwind] database or create the following table on your database
CREATE TABLE [Region] (
[RegionID] [int] IDENTITY NOT NULL PRIMARY KEY,
[RegionDescription] [nchar] (50) NOT NULL
)
INSERT INTO Region VALUES('Eastern')
INSERT INTO Region VALUES('Western')
INSERT INTO Region VALUES('Northern')
INSERT INTO Region VALUES('Southern')
Then run these 2 queries:
SELECT * FROM Region WHERE RegionDescription LIKE COALESCE('%East%', RegionDescription)
SELECT * FROM Region WHERE RegionDescription LIKE ISNULL('%East%', RegionDescription)
Technically, these 2 queries should return the same results, but LIKE comparison with COALESCE against char or nchar field returns with empty result. Can anyone provide a technical clarification? Thanks!
BTW, I have SQL Server 2000 with SP4.
-
In 2005 it works correct
-
SELECT * FROM Region WHERE RegionDescription LIKE COALESCE('%East%', RTRIM(RegionDescription))
fixes the problem
-
As well as NVARCHAR datatype for RegionDescription.
_____________
Code for TallyGenerator -
This will also work
SELECT * FROM Region WHERE RegionDescription LIKE COALESCE('%East%',convert(nvarchar,RegionDescription))
and this also
SELECT * FROM Region WHERE RegionDescription LIKE COALESCE('%East%',convert(char,RegionDescription))
Reason behind this is
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 one 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.
and
COALESCE converts all arguments to the same datatype
-
Guys, these solutions and explanations are great
Another proof of brain solving powers 
What I don't get it is, in this case, why we would have to worry about ASCII vs. Unicode data, as well as trailing space in char column. To me, I simply think that ...
Based on COALESCE T-SQL definition, all the following
COALESCE('%East%',convert(char,RegionDescription))
COALESCE('%East%',convert(nvarchar,RegionDescription))
COALESCE('%East%', RTRIM(RegionDescription))
will returns the first non-null value, which should always be '%East%'
Next, LIKE is pattern matching comparison, and
compares '%East%' to 'Eastern' or 'Eastern ' ,should always return true.
Technically, I wonder if you agree with me if I said this is a bug in SQL Server 2000? Or do you categorize it as a bug in the SQL statement?
-
Problem is not in the LIKE, but in the ISNULL / COALESCE.
ISNULL ( check_expression , replacement_value )
Arguments
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.
replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.
With ISNULL the first expression will decide the result datatype. However, with COALESCE it seems that the result will have datatype of the last expression (in your case, CHAR(50) - which means the result is not "%East%" but "%East% ". (lots of trailing spaces)... and that can't be found in the table, so no rows are returned.
Try this:
declare @table table (regionID int identity, descr char(50), descr2 char(4))
insert into @table select 'Eastern', 'abcd'
insert into @table select 'Western', NULL
select COALESCE(descr2, Descr) from @table
select ISNULL(descr2, Descr) from @table
... and you will see another way how to create nice, hard-to-find errors:
results are:
coalesce
--------------------------------------------------
abcd
Western
isnull
------
abcd
West
ISNULL and COALESCE are not the same, they are just similar. Don't ask me why and whether it is intentional... I don't know that. I just know you have to be careful with them. BTW, seeing this, I would add to the title of your post : "and ISNULL doesn't like CHAR, too!" ... but that wouldn't be precise, since truncation occurs even with varchar.
-
Slight correction of my previous post:
It seems that COALESCE does not select datatype of the last expression, but the "most appropriate" datatype - if we are talking about CHAR data, then always the longest one. See this:
declare @table table (regionID int identity, descr char(10), descr2 char(4))
insert into @table select 'Eastern', 'abcd'
insert into @table select 'Western', NULL
select COALESCE('somestrangetext', descr2) from @table
select ISNULL('somestrangetext', descr2) from @table
select COALESCE(descr2, 'somestrangetext')+'haha' from @table
select ISNULL(descr2,'somestrangetext') from @table
With COALESCE, no data truncation occurs, independent on which expression goes first - but in case of CHAR data, a shorter result will be padded with spaces (I added the "haha" to make it more visible). ISNULL uses strictly the data type of first expression, even if it means that data are truncated.
IMO this is just another reason why not use CHAR datatype - the padding is dangerous if you forget about it - and why to prefer COALESCE over ISNULL.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply