Skip to content

Latest commit

 

History

History
46 lines (40 loc) · 1.42 KB

replace-multiples.md

File metadata and controls

46 lines (40 loc) · 1.42 KB

Replace or Strip out multiple different string matches from some text.

Description

If you need to remove to replace multiple difference string conditions it can be quite code heavy unless you make use REGEXP_REPLACE Use | to alternative matches (?i) allows case of the matches to be ignored

select REGEXP_REPLACE('Some text1 or Text2 etc', r'(?i)text1|text2|text3|text4','NewValue')

Example

with pet as
(select ['Marley (hcp)','Marley (HCP)','Marley(hcp)','Marley (hcP)','Marley (CC)','Marley (cc)','Marley(cc)','Marley (rehomed)','Marley (re-homed)','Marley (stray)','Marley**','Marley        **'] as pets
), pet_list as
(
select pet_name
  from pet
  left join unnest(pets) pet_name)
select
  pet_name, 
  TRIM(REGEXP_REPLACE(pet_name, r'(?i)\(hcp\)|\(cc\)|\(re-homed\)|\(foster\)|\(stray\)|\(rehomed\)|\*\*','')) as clean_pet_name
from pet_list
+-----------------+--------------+
|pet_name         |clean_pet_name|
+-----------------+--------------+
|Marley (hcp)     |Marley        |
|Marley (HCP)     |Marley        |
|Marley(hcp)      |Marley        |
|Marley (hcP)     |Marley        |
|Marley (CC)      |Marley        |
|Marley (cc)      |Marley        |
|Marley(cc)       |Marley        |
|Marley (rehomed) |Marley        |
|Marley (re-homed)|Marley        |
|Marley (stray)   |Marley        |
|Marley**         |Marley        |
|Marley        ** |Marley        |
+-----------------+--------------+