Where Like any combination of 4 values

slc1axj · June 2021

Part of my case statement is as follows:

when o.'service category' like '%DISP%' or o.'service category' like '%O/P%' or o.'service category' like '%RESP%' or o.'service category' like '%HME%' then 'Y'

However, if service category contains anything other than the 4 codes above, I don't want it labeled a Y - so basically the service category can only contain one of the following codes (DISP, O/P, RESP or HME) - the service category can contain multiple values

Jim_Knicely · June 2021

There are a million ways to do this ... Here is one example:

verticademos=> SELECT "service category", CASE WHEN "service category" = '' THEN 'N' ELSE DECODE(REGEXP_REPLACE("service category", '\b(?:DISP|O/P|RESP|HME|,)\b', '', 1, 0), '', 'Y', 'N') END FROM o;
 service category | case
------------------+------
 A,B,C            | N
 DISP             | Y
 DISP,O/P         | Y
 RESP,O/P,A,Z     | N
 O/P,HME,Q        | N
                  | N
(6 rows)

marcothesane · June 2021

What does service_category normally contain? Your LIKE predicate suggests that you have something before one of the codes you mention and something after one of the codes you mention. What does service_category contain when your expressions returns 'N'? Depending on the answer, you could or could not replace the expression with an IN() predicate, or a REGEXP_LIKE() function. Can you share some examples?

We're Moving!

Create My New Community Account Now

Where Like any combination of 4 values

Best Answer

Answers

Leave a Comment