mysql: replace \ (backslash) in strings

Question

I am having the following problem:

I have a table T which has a column Name with names. The names have the following structure:

A\\B\C

You can create on yourself like this:

create table T ( Name varchar(10));

insert into T values ('A\\\\B\\C');

select * from T;

Now if I do this:

select Name from T where Name = 'A\\B\C';

That doesn't work, I need to escape the \ (backslash):

select Name from T where Name = 'A\\\\B\\C';

Fine.

But how do I do this automatically to a string Name?

Something like the following won't do it:

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

I get: A\\\BC

Any suggestions?

Many thanks in advance.

Not sure I follow. Do you expect the inserted value to be found without specifying it the same way it was inserted? — jefflunt
– jefflunt, Commented Apr 8, 2012 at 0:33
Have you considered using a different delimiter? If the issue is archaic data, a simple script that updates the old data would suffice. — flagoworld
– flagoworld, Commented Apr 8, 2012 at 0:49
Maybe I didn't explain it very well. Turns out if I use quote() for the Name I get what I want, i.e. the \ is taken literally. — Comber
– Comber, Commented Apr 8, 2012 at 23:29

Rahul · Accepted Answer · 2013-04-01 11:25:04Z

4

You have to use "verbatim string".After using that string your Replace function will look like this

Replace(@"\", @"\\")

I hope it will help for you.

answered Apr 1, 2013 at 11:25

Rahul

5,6556 gold badges38 silver badges58 bronze badges

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

Comments

Bohemian · Accepted Answer · 2012-08-08 17:48:28Z

1

The literal A\\B\C must be coded as A\\\\A\\C, and the parameters of replace() need escaping too:

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

output (see this running on SQLFiddle):

A\\B\C         A\\\\B\\C

So there is little point in using replace. These two statements are equivalent:

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

edited Aug 8, 2012 at 17:48

answered Apr 8, 2012 at 3:55

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

2 Comments

Jestep Over a year ago

select replace('A\\B\C', '\', '\\'); returns an error, because the backslash is escaping the apostrophe in the search parameter.

Bohemian Over a year ago

@Jestep Thanks for picking that up. I corrected the mistake. Cheers

eablex · Accepted Answer · 2012-09-02 09:59:55Z

1

Usage of regular expression will solve your problem.

This below query will solve the given example.

  1) S\\D\B

select * from T where Name REGEXP '[A-Z]\\\\\\\\[A-Z]\\\\[A-Z]$';

if incase the given example might have more then one char

2) D\\B\ACCC

select * from T where Name REGEXP '[A-Z]{1,5}\\\\\\\\[A-Z]{1,5}\\\\[A-Z]{1,5}$';

note: i have used 5 as the max occurrence of char considering the field size is 10 as its mentioned in the create table query.

We can still generalize it.If this still has not met your expectation feel free to ask for my help.

answered Sep 2, 2012 at 9:59

eablex

595 bronze badges

Comments

Barmar · Accepted Answer · 2012-09-03 15:13:52Z

You're confusing what's IN the database with how you represent that data in SQL statements. When a string in the database contains a special character like \, you have to type \\ to represent that character, because \ is a special character in SQL syntax. You have to do this in INSERT statements, but you also have to do it in the parameters to the REPLACE function. There are never actually any double slashes in the data, they're just part of the UI.

Why do you think you need to double the slashes in the SQL expression? If you're typing queries, you should just double the slashes in your command line. If you're generating the query in a programming language, the best solution is to use prepared statements; the API will take care of proper encoding (prepared statements usually use a binary interface, which deals with the raw data). If, for some reason, you need to perform queries by constructing strings, the language should hopefully provide a function to escape the string. For instance, in PHP you would use mysqli_real_escape_string.

But you can't do it by SQL itself -- if you try to feed the non-escaped string to SQL, data is lost and it can't reconstruct it.

the Tin Man · Accepted Answer · 2012-10-02 08:42:47Z

0

You could use LIKE:

SELECT NAME FROM T WHERE NAME LIKE '%\\\\%';

edited Oct 2, 2012 at 8:42

the Tin Man

161k44 gold badges222 silver badges308 bronze badges

answered Aug 18, 2012 at 11:42

RajKannan

13 bronze badges

Comments

Rohan Durve · Accepted Answer · 2012-04-08 04:02:24Z

-3

Not exactly sure by what you mean but, this should work.

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

It's basically going to replace \ whereever encountered with \\ :)

Is this what you wanted?

answered Apr 8, 2012 at 4:02

Rohan Durve

153 bronze badges

1 Comment

András Gyömrey Over a year ago

That won't even "compile" you have a syntax error escaping the second parameter.

Collectives™ on Stack Overflow

mysql: replace \ (backslash) in strings

6 Answers 6

Comments

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related