MySQL Replace single backslash

Question

Trying to replace a single backslash in a large db. Was going to run an Update Replace query on one column, but I can't figure out how to replace a single backslash. There was some poor updates done and these slashes need to be changed to another character, they are not escaping anything and do not perform any relevant function.

SELECT
REPLACE (
    "Some\s string\s with slashe\s",
    '\\',
    '  something  '
)

When I run this, the output is: "Somes strings with slashes"

Is there any way to do a true replace on a single slash? No matter what I put in the replace parameter, it just eliminates the single backslash, but doesn't actually replace it. Not sure if this is a bug or I'm just missing something.

I have tried:

SELECT
REPLACE (
    "Some\s string\s with slashe\s",
    '\',
    '  something  '
)

and I get:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\')' at line 1

John Woo · Accepted Answer · 2012-08-08 15:26:33Z

4

REPLACE( ) doesn't need to be escaped

select replace("Some\s string\s with slashe\s", '\\', '  something  ');

UPDATE 1

I think you want to permanently replace it. Right? Use UPDATE not SELECT

UPDATE tableName
SET    columnName = replace(columnName, '\\', '#');

edited Aug 8, 2012 at 15:26

answered Aug 8, 2012 at 15:20

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jestep Over a year ago

As commented above, I get "[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\')' at line 1".

John Woo Over a year ago

@Jestep I just updated the answer. Do you want to permanently replace the column? or you want only to project it?

Jestep Over a year ago

I see now. Just ran a test on a few columns. The Update works, but if I simply try to run a select it shows the incorrect output. I was using the select on a static string just to verify the output would be correct.

John Woo Over a year ago

It's because SELECT and UPDATE are two different things. Anything you do with SELECT doesn't change anything on the database.

Imran Luhur · Accepted Answer · 2023-04-18 20:02:28Z

0

Try This

SELECT REPLACE ("Some\s string\s with slashe\s",'\"','' )

answered Apr 18, 2023 at 20:02

Imran Luhur

4773 silver badges15 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:54:03Z

-1

The parameters of replace() don't need escaping:

select replace('A\B\C', '\', '\');

output:

A\\\\B\\C

So this will work:

select Name from T where Name = replace('A\\B\C', '\', '\\');

AS seen HERE

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Aug 8, 2012 at 15:18

Rahul P

1,1134 gold badges17 silver badges32 bronze badges

1 Comment

Jestep Over a year ago

I get "[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\')' at line 1"

Collectives™ on Stack Overflow

MySQL Replace single backslash

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related