snippetsqlMinor

How do I remove backslashes from records in MySQL

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

backslashesrecordsremovemysqlhowfrom

Problem

Long story short, I have backslashes in my data due to PHP magic quotes. I have straightened out what was wrong there, but have some records now which have unnecessary backslashes.

1 | This is an example of \' single, \" double, and \\ backslash (in the messed up records)
2 | This example is how it gets stored now \ backslash ' single " double

When I want to output my data, I need to use strip slashes to fix the issue and get the following output:

1 | This is an example of ' single, " double, and \ backslash

But then this one is wrong:

2 | This example is how it gets stored now backslash ' single " double

Thus stripslashes in my php is removing a slash which isn't technically used to escape anything.

I want to not have to use strip slashes, since my data should be clean like this:

1 | This is an example of ' single, " double, and \ backslash (how data should be)

How can I query to replace \' and \" and \\ with ' " and \?

Solution

DROP TABLE IF EXISTS tmpTable;
CREATE TEMPORARY TABLE IF NOT EXISTS tmpTable ( BadText VARCHAR(100));

INSERT INTO tmpTable
SELECT 'This is an example of \\\' single, \\\"double, and \\\\ backslash';

select BadText from tmpTable;

update tmpTable
set BadText = replace(replace(replace(BadText,'\\\'','\''),'\\\"','"'),'\\\\','\\');

select BadText as GoodText from tmpTable;

Code Snippets

DROP TABLE IF EXISTS tmpTable;
CREATE TEMPORARY TABLE IF NOT EXISTS tmpTable ( BadText VARCHAR(100));

INSERT INTO tmpTable
SELECT 'This is an example of \\\' single, \\\"double, and \\\\ backslash';

select BadText from tmpTable;

update tmpTable
set BadText = replace(replace(replace(BadText,'\\\'','\''),'\\\"','"'),'\\\\','\\');

select BadText as GoodText from tmpTable;

Context

StackExchange Database Administrators Q#2261, answer score: 6

Revisions (0)

No revisions yet.