MYSQL Replace string that contains specific substring

Question

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?

Isolated · Accepted Answer · 2022-10-11 17:14:35Z

1

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

answered Oct 11, 2022 at 17:14

Isolated

6,4942 gold badges12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL Replace string that contains specific substring

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related