Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 \?

share|improve this question

3 Answers 3

up vote 5 down vote accepted
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;
share|improve this answer
1  
tried this out on a few fields in my development environment, seems to be doing exactly what I need, removing escaping backslashes, but leaving intact actual backslashes. –  Bob Apr 20 '11 at 19:27
    
all is well. thanks for you answer. –  Bob Apr 27 '11 at 20:17

A REGEX_REPLACE would be the easiest for this since you can remove more than one slash and replace it with nothing. Unfortunately, there is no such thing in MySQL but there is a nice UDF for RegExReplace on Launchpad (need to compile it yourself but that shouldn't be a problem IMO).

So with the UDF installed, something like

SELECT REGEX_REPLACE(your_column, '.*backslash{2,}.*', 'backslash') FROM table 

Haven't tested it but it should work. It uses the same REGEX as already supported.

share|improve this answer

Since you are using PHP, you can use the stripslashes() function. They work with magic quotes too. Click here to see the PHP doc.

share|improve this answer
    
bugs.php.net/bug.php?id=19947 and bugs.php.net/bug.php?id=27848. As I stated in my question, I can't simply use stripslashes, I want to actually clean up my data. –  Bob Apr 20 '11 at 14:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.