Like in CASE statement not evaluating as expected

Question

Given this data:

CREATE TABLE tmpTable(
fldField varchar(10) null);

INSERT INTO tmpTable
SELECT 'XXX'
UNION ALL 
SELECT 'XXX'
UNION  ALL
SELECT 'ZZZ'
UNION  ALL
SELECT 'ZZZ'
UNION  ALL
SELECT 'YYY'

SELECT
CASE WHEN fldField like 'YYY' THEN 'OTH' ELSE 'XXX' END AS newField
FROM tmpTable

The expected resultset is:
XXX
XXX
XXX
XXX
OTH

What situation would casue SQL server 2000 to NOT find 'YYY'? And return the following as the resultset:
XXX
XXX
XXX
XXX
XXX

The problem is with the like 'YYY', I have found other ways to write this to get it to work, but I want to know why this exact method doesn't work. Another difficulty is that it works in most of my SQL Server 2000 environments. I need to find out what is different between them to cause this. Thanks for your help.

Answer 1

You aren't specifying what you are selecting and checking the CASE against...

SELECT  CASE fldField WHEN 'YYY' 
THEN 'OTH' ELSE 'XXX' END AS newField FROM tmpTable

Answer 2

how about fldField = '%YYY%'

Answer 3

It worked as expected on my SQL 2005 installation. If it works on other machines, it sounds like you've got an environment difference. Try comparing your connection properties in SQL Server Management Studio for a connection that works and one that doesn't to see if you can figure out what the differences are.

Answer 4

I am an Oracle person, not a SQL*Server person, but it seems to me you should be either:-

SELECT
   CASE WHEN fldField like '%YYY%' THEN 
             'OTH' 
        ELSE 'XXX'
   END AS newField 
FROM
   tmpTable

or ...

SELECT
   CASE WHEN fldField = 'YYY' THEN 
             'OTH' 
        ELSE 'XXX'
   END AS newField 
FROM
   tmpTable

The second is the direction I'd go in, as at least in Oracle equality resolves quicker than like.

Answer 5

When you use LIKE without specifying any search criteria, it behaves like an = comparison. In your example, I would expect it to work properly. In your real data, you probably have a hidden (non-printable) character in your data (think about Carriage Return, Line Feed, Tab, etc....).

Take a look at this example...

Declare @tmpTable TABLE(
fldField varchar(10) null);

INSERT INTO @tmpTable
SELECT 'XXX'
UNION ALL 
SELECT 'XXX'
UNION  ALL
SELECT 'ZZZ'
UNION  ALL
SELECT 'ZZZ'
UNION  ALL
SELECT 'YYY'
UNION  ALL
SELECT 'YYY' + Char(10)

SELECT CASE WHEN fldField like 'YYY' THEN 'OTH' ELSE 'XXX' END AS YourOriginalTest,
       CASE WHEN fldField like 'YYY%' THEN 'OTH' ELSE 'XXX' END AS newField
FROM   @tmpTable

You'll notice that the last piece of data I added is YYY and a Line Feed. If you select this data, you won't notice the line feed in the data, but it's there, so your LIKE condition (which is acting like an equal condition) doesn't match.

The common 'hidden' characters are Tab, Carriage Return, and Line Feed. To determine if this is causing your problem...

Select *
From Table
Where  Column Like '%[' + Char(10) + Char(9) + Char(13) + ']%'

Answer 6

I ran the code on a SQL 2000 box and got identical results. Not only that, but when I ran some additional code to test I got some VERY bizarre results:

CREATE TABLE dbo.TestLike ( my_field varchar(10) null);
GO
CREATE CLUSTERED INDEX IDX_TestLike ON dbo.TestLike (my_field)
GO
INSERT INTO dbo.TestLike (my_field) VALUES ('XXX')
INSERT INTO dbo.TestLike (my_field) VALUES ('XXX')
INSERT INTO dbo.TestLike (my_field) VALUES ('ZZZ')
INSERT INTO dbo.TestLike (my_field) VALUES ('ZZZ')
INSERT INTO dbo.TestLike (my_field) VALUES ('YYY')
GO

SELECT
      my_field,
      case my_field when 'YYY' THEN 'Y' ELSE 'N' END AS C2,
      case when my_field like 'YYY' THEN 'Y' ELSE 'N' END AS C3,
      my_field
FROM dbo.TestLike
GO

My results:

my_field   C2   C3   my_field
---------- ---- ---- ----------
N          XXX  N    XXX
N          XXX  N    XXX
Y          YYY  N    YYY
N          ZZZ  N    ZZZ
N          ZZZ  N    ZZZ

Notice how my_field has two different values in the same row? I've asked some others at the office here to give it a quick test. Looks like a bug to me.

Answer 7

What a cute bug. I think I know the cause. If I'm right, then you'll get the results you expect from:

SELECT
  CASE
    WHEN fldField like 'YYY       '  -- 7 spaces
    THEN 'OTH'
    ELSE 'XXX'
  END as newField
from tmpTable

The bug is that varchar(10) is behaving like char(10) is supposed to. As for why it doesn't, you'll need to understand the old trivia question of how two strings with no metacharacters can be = but not LIKE each other.

The issue is that a char(10) is internally supposed to be space padded. The like operator does not ignore those spaces. The = operator is supposed to in the case of chars. Memory tells me that Oracle ignores spaces for strings in general. Postgres does some tricks with casting. I have not used SQL*Server so I can't tell you how it does it.

Answer 8

Check your service pack. After upgrading my SQL 2000 box to SP4 I now get the correct values for your situation.

I'm still getting the swapped data that I reported in my earlier post though :(

If you do SELECT @@version you should get 8.00.2039. Any version number less than that and you should install SP4.