0

I have a database where I need to run sql scripts in order to export the new data from a 3rd party. The data then would also need to be cleaned by which I am doing via MySQL workbench. I am currently stuck at the part where I need to clean the strings containing a combination of a backslash and apostrophe together.

For example,

June\'s Wedding

I want the string to be

June's Wedding

So first I will be searching for such strings right? So I queried

SELECT columnname FROM tablename WHERE columnname LIKE '%\\\\''%'

to see whether my REPLACE() will work or not before I proceed to UPDATE I change the query to this

SELECT REPLACE(columnname,'\'','''') FROM tablename WHERE columnname LIKE '%\\\\''%'

but there is no changes applied to the string as it remains

| REPLACE(columnname,'\'','''')|
+------------------------------+
| June\'s Wedding              |

How do I resolve this?

Also I am abit confused with the escape characters such as do I need to use the double single-quote '' in REPLACE() or use \'?

Nadhirah21
  • 133
  • 1
  • 1
  • 9

1 Answers1

0

Try this.

For Update using Double Quote

UPDATE tbl1 SET col1 = REPLACE(col1, "\\'s", "") WHERE col1 LIKE "%\\'s%";

For Update using Single Quote

UPDATE tbl1 SET col1 = REPLACE(col1, '\\\'s', '') WHERE col1 LIKE '%\\\'s%';

Add \ before the \ and before ' if you're going to use single quote for strings.

Check this out Single Quote, Double Quote, and Backticks in MySQL Queries

If you need to use single quotes and double quotes in a string that contains both a contraction and a quote, you will need to use the backslash ‘' to cancel out the following character. For example: a string containing this ' will recognize the backslash as an instruction to cancel out the single quote’s syntactical meaning and instead insert it into the string as an apostrophe.

And this How to escape apostrophe (') in MySql?

A ' inside a string quoted with ' may be written as ''.

Kuro Neko
  • 795
  • 12
  • 19