SQL Server bug using LIKE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server bug using LIKE

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-10-25 : 21:37:28

robert writes "I think I've found a bug with SQL Server using combination of CASE and LIKE.

I appreciate that normally you wouldn't code
'literal' LIKE 'literal' (ie without wildcards)

but I use the LIKE operator to avoid issues with case sensitivity and whitespace, and also because I'm generating the SQL from other code and the literals may contain wildcards.

I stumbled over the following problem when my search value DID NOT happen to contain a wildcard. I was doing complex joins on large tables, so I've simplified it down to the root issue.

I would love it if someone out there could confirm they get the same result on other versions of SQL Server.

My platform is:
NT v4 sp 6
Microsoft SQL Server 2000

/*----------------------------------------*/
CREATE TABLE tA (sentence varchar(100))
INSERT INTO tA (sentence) VALUES ('Bug with SQL')
GO

SELECT
Question =
CASE
WHEN (tA.sentence Like 'Bug with SQL') THEN 0
ELSE 1
END,
NULL AS SecretAnswer
INTO tB
FROM tA
WHERE tA.sentence IS NOT NULL

SELECT * FROM tB
GO

/* I would have thought the result should be 0,
but instead I get garbage */"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources