CODEWITHSUNDEEP
mysql
Edgar
Edgar

Reputation: 1131

MySQL - replacing specific character inside of column string

Got a column that keeps strings like such:

{"plan-36";"id-36";}

I want to check each string of this column, and if matches my criteria, it would change a specific character of this string (for example would change number 36 to 99). Here's what i got so far:

SELECT string_column
FROM example_table
WHERE 
  INSTR(string_column,'plan-36') > 0
OR
  INSTR(string_column,'id-36') > 0

This only returns rows that has 'plan-36' or 'id-36' string in them. What i need is - if this row contain such strings, i need to change the 36 value, to let's say 99.

Which sql functions do i need for this?

Upvotes: 1

Views: 678

Answers (1)

exussum
exussum

Reputation: 18550

UPDATE example_table
SET string_column = REPLACE(string_column, "36", "99")
WHERE 
  INSTR(string_column,'plan-36') > 0
OR
  INSTR(string_column,'id-36') > 0

would be the syntax, this will update all instances of 36 to 99, Be careful and test on backup data first.

It may be better to use this

  SET string_column = REPLACE(string_column, "-36", "-99")

which includes the dash

Upvotes: 2

Related Questions