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

Example:

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?

CodePudding user response：

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