CODEWITHSUNDEEP
mysqlsqlhivehiveql
lydias
lydias

Reputation: 841

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!

Upvotes: 0

Views: 51

Answers (2)

Ibrahim Abu Lubbad
Ibrahim Abu Lubbad

Reputation: 423

just write the case in this 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 newname

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270091

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.

Upvotes: 1

Related Questions