Where Like any combination of 4 values

slc1axjslc1axj Vertica Customer

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

Best Answer

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator
    Answer ✓

    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)


  • Options
    marcothesanemarcothesane - Select Field - Administrator

    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?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file