CODEWITHSUNDEEP
mysqlregexreplaceregex-replace
Mariana
Mariana

Reputation: 81

MYSQL Replace string that contains specific substring

Basically, I want to substring all strings that contain the word COMPRIMIDO to keep only COMPRIMIDO

Example:

medication_title medication_type medication_result
ZYTIGA 500 MG COMPRIMIDO REVESTIDO COMPRIMIDO
VERZENIOS 50 MG COMPRIMIDO MOLE COMPRIMIDO

I tried using replace function:

REPLACE(REPLACE(medication_type, 'COMPRIMIDO REVESTIDO', 'COMPRIMIDO'), 'COMPRIMIDO MOLE', 'COMPRIMIDO') as medication_result

But I think there are easier ways to do this, especially thinking about situations where we can have several string variations with "COMPRIMIDO"

Ideas?

Upvotes: 0

Views: 179

Answers (1)

Isolated
Isolated

Reputation: 6454

Easier to use a case expression with LIKE.

select medication_title, medication_type, 
 case 
  when upper(medication_type) like '%COMPRIMIDO%' then 'COMPRIMIDO' 
  else medication_type 
 end medication_result
from my_data

Upvotes: 1

Related Questions