Replacing substrings in MySQL

Question

I have some sentences in string in MySQL. And I need to replace substrings such as 'My' to 'my' if this word not first in sentence. How I can doing this?

Answer 1

CHAR, REPLACE, REPEAT, etc. I'd recommend reading mySQL ref: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html

Answer 2

If you just want to replace several words, you can replace them, using this approach:

UPDATE str_test SET str = REPLACE(str, ' My', ' my')

fiddle

As the words inside the text will be preceded by space. But if you want a regexp replace, it will be a more difficult task:

How to count words in MySQL / regular expression replacer?

https://dba.stackexchange.com/questions/15250/how-to-do-a-case-sensitive-search-in-where-clause

Answer 3

MySql support for string is very limited. A quick solution would be to use something like this:

SELECT
  CONCAT(
    LEFT(col, 1),
    REPLACE(SUBSTRING(col, 2), 'My', 'my')
  )

Please see fiddle here. This will replace all the strings My to my, except the first one.

Or maybe this:

SELECT
  col,
  RTRIM(REPLACE(CONCAT(col, ' '), ' My ', ' my '))
FROM
  yourtable

that will replace all whole words except the first one.