Sql as a sentence with many meanings

Question

I have 4 columns in my table. Now, since this is more of a data cleansing task, I am not really looking at performance that much. But still, I would like to know the possible options.

Take a look at the request 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%'; 

Like DEP, I have 10 more matches. I need to repeat each match for all 4 address lines. Is there a better way to do this? I personally hate writing over and over 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') 

Answer 1

You can try regexp_like

Answer 2

You can try writing a stored procedure, and the "DEP" part is entered as a parameter. Then simply combine the results of all calls to stored-proc.

Answer 3

 CREATE VIEW VW_dsopi_person_addr_rule AS SELECT Primary_key , src_address_line1 AS src_address_line FROM dsopi_person_addr_rule UNION SELECT Primary_key , src_address_line2 FROM dsopi_person_addr_rule UNION SELECT Primary_key , src_address_line3 FROM dsopi_person_addr_rule SELECT Primary_key , src_address_line4 FROM dsopi_person_addr_rule DELETE FROM VW_dsopi_person_addr_rule WHERE Primary_key in (SELECT Primary_key FROM VW_dsopi_person_addr_rule WHERE src_address_line like '%DEP%) DELETE D1 FROM VW_dsopi_person_addr_rule D1 WHERE EXISTS (SELECT 1 FROM VW_dsopi_person_addr_rule V1 WHERE V1.Primary_key_value1 = D1.Primary_key_value1 AND V1.Primary_key_value2 = D1.Primary_key_value2 AND V1.src_address_line like '%DEP%')