CODEWITHSUNDEEP
sqlsql-servercase
user970225
user970225

Reputation: 145

Using "like" inside a "case" statement with 2 different fields

I have 2 fields in my table on which i need a case statement.

Col1          Col2     Col3
abc.txt       Y        0
def.txt       N        0
bbck.txt      Y        1

The Col3 values are based on the Col1 and Col2 values in the following manner.
Y = 1 and N = 0. So all the values in Col2 that are Y shall become 1 in col3, and Nin Col2 will become 0 in Col3, UNLESS the col1 value ends with %c.txt. As you can see since the abc.txt ends with %c.txt the value in col3 becomes 0.

I know this can be done with a CASE statement nested maybe to get this done. Does anyone know how to?

here's my code

SELECT 
  CASE Col2
    WHEN 'Y' THEN '1'
    WHEN 'N' THEN '0'
  ELSE
(CASE WHEN [Col1] LIKE '%c.txt' THEN '0'
 END)
  END
  AS Col3,
 *
FROM Tabl1

Hope this gives an idea

Upvotes: 3

Views: 28019

Answers (4)

Standage
Standage

Reputation: 1517

This might be what your looking for:

 SELECT * FROM Tabl1
 cross apply
    (
      select Col3 =
      CASE  
            WHEN Tabl1.Col2 = 'Y' then '1'
            WHEN Tabl1.Col2 = 'Y' then '1'
            WHEN RIGHT(Tabl1.Col1, 6) = 'c.txt' then '0'
            END 
) as Col3

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

I don't think your spec is clear enough e.g. is the comma before the word "UNLESS" suposed to incidicate that the following clause applies to both the previous clauses?

There seems to be four possible combinations:

1) Col1 LIKE '%c.txt'
   AND Col2 = 'Y'

2) Col1 LIKE '%c.txt'
   AND Col2 = 'N'

3) Col1 NOT LIKE '%c.txt'
   AND Col2 = 'Y'

4) Col1 NOT LIKE '%c.txt'
   AND Col2 = 'N'

You say

i need a case statement

Is this true? With SQL, the same thing can always be achieved multiple ways (which is why it is so hard to build a good optimizer :) I think a spec should state what is required and not place unreasonable restrictions on how to implement it.

The following uses UNION rather than case and if you had considered this from the start then maybe your spec would be better ;)

WITH Tabl1
     AS 
     (
      SELECT * 
        FROM (
              VALUES ('abc.txt', 'Y'),
                     ('def.txt', 'N'),
                     ('bbck.txt', 'Y'),
                     ('disc.txt', 'N')
             ) AS T (Col1, Col2)
     )
SELECT '0' AS Col3, *
  FROM Tabl1
 WHERE Col1 LIKE '%c.txt'
       AND Col2 = 'Y'
UNION
SELECT '0' AS Col3, *
  FROM Tabl1
 WHERE Col1 LIKE '%c.txt'
       AND Col2 = 'N'
UNION
SELECT '1' AS Col3, *
  FROM Tabl1
 WHERE Col1 NOT LIKE '%c.txt'
       AND Col2 = 'Y'
UNION
SELECT '0' AS Col3, *
  FROM Tabl1
 WHERE Col1 NOT LIKE '%c.txt'
       AND Col2 = 'N';

P.S. I coded this to match the accepted answer but I'm not convinced it is correct!

Upvotes: 0

Hector Sanchez
Hector Sanchez

Reputation: 2317

SELECT 
  CASE WHEN Col2 LIKE '%c.txt' THEN '0'
       ELSE
       CASE 
            WHEN Col2  = 'N'  THEN '0'
            WHEN Col2  = 'Y' THEN '1'
       END
  END AS COL3
FROM Tabl1

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Maybe:

SELECT 
    CASE 
      WHEN Col2 = 'N'
        OR Col1 LIKE '%c.txt'
          THEN '0'
      WHEN Col2 = 'Y'
          THEN '1'
    END AS Col3
  , * 
FROM Tabl1

Upvotes: 4

Related Questions