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.

Answer 1

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?

Answer 2

You could use LIKE:

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

Answer 3

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.

Answer 4

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';

Answer 5

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.

Answer 6

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.