Using "like" inside a "case" statement with 2 different fields
22,651
Maybe:
SELECT
CASE
WHEN Col2 = 'N'
OR Col1 LIKE '%c.txt'
THEN '0'
WHEN Col2 = 'Y'
THEN '1'
END AS Col3
, *
FROM Tabl1
Author by
user970225
Updated on July 09, 2022Comments
-
user970225 almost 2 years
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 theCol1
andCol2
values in the following manner.
Y = 1
andN = 0
. So all the values inCol2
that areY
shall become1
incol3
, andN
inCol2
will become0
inCol3
, UNLESS thecol1
value ends with%c.txt
. As you can see since theabc.txt
ends with%c.txt
the value incol3
becomes0
.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
-
user970225 over 12 yearsperfect answer. Thanks a lot.
-
ypercubeᵀᴹ over 12 yearsThe
WHEN Col2 = 'Y' THEN '1'
can be replaced withELSE '1'
ifCol2
has onlyY
andN
values and nothing else.