SQL how to prevent masking by like statement

I’m dealing with a masking issue with the like statement such that:

   case
   when name like 'PO_UTI_%' then 'UTI' 
   when name like 'PO_UTI_ADR_%' then 'UTI ADDRESS'
   when name like 'PO_OP_%' then 'OP' 
   when name like 'PO_OP_HH_%' then 'OP HH' 
   end newname

The newname UTI will be masking UTI ADDRESS and OP will be masking OP HH, how should I avoid this masking issue?

Thank you!

Answer

You put the conditions in a better order:

(case when name like 'PO_UTI_ADR_%' then 'UTI ADDRESS'
      when name like 'PO_UTI_%' then 'UTI' 
      when name like 'PO_OP_HH_%' then 'OP HH' 
      when name like 'PO_OP_%' then 'OP' 
 end) as newname

Actually, it may be better to order them by length:

(case when name like 'PO_UTI_ADR_%' then 'UTI ADDRESS'
      when name like 'PO_OP_HH_%' then 'OP HH' 
      when name like 'PO_UTI_%' then 'UTI' 
      when name like 'PO_OP_%' then 'OP' 
 end) as newname

This should help you ensure that there are no “masking” conflicts.