Serious wildcard issue with matching comparisons using LIKE
-
I have come across an issue that has been confirmed as a bug in SQL 2005
I’m trying to do a LIKE comparison but using hyphen(-) as a character instead of a wildcard.
In the documentation (MSDN ) http://msdn2.microsoft.com/en-us/library/ms187489.aspx it is clearly stated that
You can search for wildcard characters. There are two methods for specifying a character that would ordinarily be a wildcard:…
[Second method]...
· Use square brackets ([ ]) to enclose the wildcard by itself. To search for a hyphen (-), instead of using it to specify a search range, use the hyphen as the first character inside a set of brackets:
WHERE ColumnA LIKE '9[-]5'
When this is done in SQL 2005 SP1 nothing is returned.
When this is done in SQL 2000 the record “9-5” is returned.
Also in SQL 2000 the query …. WHERE ColumnA LIKE ‘9-5’ … would return results as the hyphen is not enclosed in square brackets.
I’m aware that there have been syntax changes in TSQL for SQL 2005 (such us not being able to use *=, =* for left right joins) but have found nothing related to this issue except the bug mentioned above.
This is quite a serious issue and I can’t believe Microsoft have missed it therefore there must be some documentation somewhere. From my POC’s built to test this issue the Microsoft documentation is wrong or I’ve seriously misunderstood something.
Could anyone enlighten me?
-
Sounds strange... Dashes are not wildcard characters (underscores are) so it shouldn't need to be escaped.
I tried the following (SQL2k5 SP1):
create
table wcard (col1 varchar(24))
insert
into wcard
values
('9-5')
insert
into wcard
values
('9 - 5')
insert
into wcard
values
('9 to 5')
insert
into wcard
values
('9 _ 5')
insert
into wcard
values
('9_5')
Then I ran the following queries. They all ran just as expected:
select
* from wcard
where
col1 like '9-5%'
This one returned just one row
select
* from wcard
where
col1 like '9_5'
This one returned two rows
select
* from wcard
where
col1 like '9%5'
This returned all rows
select
* from wcard
where
col1 like '9|_5' escape '|'
and finally this one returned one row.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Dashes are intermittantly referred to as wildcards by just about everyone but you're correct they're not...I think you would probably refer to them as match comparison operators or something similar.
They're used to set a range eg '[A-Z]ot' will return Bot, Got, Not etc
I really appreciate the queries you attempted but the one you didn't try was the one that the documentation refers to which is '9[-]5'. I'd be really interested in your results.
In SQL 2000 '9[-]5' would return a result for '9-5' but on my installation of SQL 2005 it doesn't.
SQL 2000 was intelligent enough to return a result for '9-5' as well which SQL 2005 doesn't do.
The documentation also makes a point of the fact that the dash/hyphen will look for a dash(as opposed to treating like a wildcard) if it is the first charcter after a square bracket
Note that in the following quote(directly from MSDN) Microsoft refers to a dash/hyphen as being a wildcard.
"The table shows the use of wildcards enclosed in square brackets."
LIKE '[a-cdf]'
a, b, c, d, or f
LIKE '[-acdf]'
-, a, c, d, or f
There are two main documents I'm refering to in Books Online:
"LIKE (Transact-SQL)"
and
"Pattern Matching in Search Conditions"
-
You're right, I did leave that one off... I actually did do it but missed it in the cut and paste operation.
select * from wcard
where col1 like '9[-]5'
returned '9-5' just as expected.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
ha ha...my machine must be having a fit. Just as our next product release is due ... that would be about right.
Thanks for your help
-
hang on a sec...
if your still there dc could you try exactly the same thing but with nvarchar data types instead of varchar. I do tend to rebuild machines quite frequently and this ensures a pretty stable foundation to develop on and it would be extremely odd for this to be a bad install/upgrade of SQL or Windows
thanks in advance
-
Using Unicode made no difference. I just created a new column (Col2 nvarchar(24)) and ran the same queries (only the where clause changed to Col2) and got exactly the same results.
That's really strange that you'd be having this issue. I can't think of a reason for it. Collations and code pages shouldn't have any effect. Obviously, the edition of SQL Server shouldn't either. Are you running SP1? I don't remember this being a fixed issue in SP1, but who knows? If you haven't I'd say apply the service pack and try it again.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Yes I'm on SP1
it is a really odd one ... sql 2000 is giving different results to sql 2005 which is why I initially thought it was a bug or I was doing something daft. When I saw the Microsoft fix for exactly the same issue on SQL 2005 Mobile all the alarm bells went off. (http://support.microsoft.com/kb/920116)
But your results match what the documentation states whereas mine don't so it'll have to go down as something wrong with my installation which is very odd. I've actually had to show it to a couple of colleagues to prove to myself I'm not going mad. I'll build a fresh machine tomorrow and see how it goes.
One other issue that might be a factor is that at the moment all our databases are set to a compatibility of 80 rather than 90. This is the only factor where I think my environment may differ to yours. Collation is default Latin_general_ci_as (or something similar). The code pages are default as well.
-
... One other issue that might be a factor is that at the moment all our databases are set to a compatibility of 80 rather than 90. This is the only factor where I think my environment may differ to yours. Collation is default Latin_general_ci_as (or something similar). The code pages are default as well ....
so you upgraded a sql2000 instance to sql2005.
MS does not change the dblevel to 90 when upgrading. You should do so yourself if you can.
select 'Exec sp_dbcmptlevel @dbname = ''' + name + ''' , @new_cmptlevel = 90 ;' + char(10) + 'Go'
from master.sys.databases
where compatibility_level <> 90
order by name
btw all my queryresults are the same as dcpeterson's using his testcases.
SQL2005 x64EE sp1
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply