sql like clause with many values

oracle, sql, sql-like

Solution

You can try the regexp_like function

Problem

I have 4 columns in my table. Now since this is more of a data cleanup task I am not really that particular about the performance. But still I would like to know the possible options. Look at the query below: ``` SELECT * FROM dsopi_person_addr_rule ADDR WHERE addr.src_address_line1 LIKE '%DEP%' OR addr.src_address_line2 LIKE '%DEP%' OR addr.src_address_line3 LIKE '%DEP%' OR addr.src_address_line4 LIKE '%DEP%'; ``` Similar to DEP I have 10 more matches. I need to repeat each match for all the 4 address lines. Is there a better way to do it? I personally hate writing again and again. **Updated: Below is the answer ``` SELECT * FROM dsopi_person_addr_rule ADDR WHERE regexp_like (UPPER(addr.src_address_line1), 'DEP|DPT$|ABT|DIP.|DIPART|AFDEL|AVDEL|AVD.|DIV|PGRD|PGP|PPG') ```

Original source