mysql：替换字符串中的 \（反斜杠）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10059450/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql: replace \ (backslash) in strings
提问by user1316758
I am having the following problem:
我有以下问题：
I have a table T which has a column Name with names. The names have the following structure:
我有一个表 T，它有一列名称和名称。名称具有以下结构：
A\\B\C
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
我得到： A\\\BC
Any suggestions?
有什么建议？
Many thanks in advance.
提前谢谢了。
回答by Rahul
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.
我希望它会对你有所帮助。
回答by Bohemian
The literal A\\B\Cmust be coded as A\\\\A\\C, and the parameters of replace()need escaping too:
文字A\\B\C必须编码为A\\\\A\\C，并且参数也replace()需要转义：
select 'A\\B\C', replace('A\\B\C', '\', '\\');
output (see this running on SQLFiddle):
输出（请参阅在 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';
回答by eablex
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.
注意：我使用了 5 作为 char 的最大出现次数，因为它在创建表查询中提到的字段大小是 10。
We can still generalize it.If this still has not met your expectation feel free to ask for my help.
我们仍然可以概括它。如果这仍然没有达到您的期望，请随时寻求我的帮助。
回答by Barmar
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 INSERTstatements, but you also have to do it in the parameters to the REPLACEfunction. There are never actually any double slashes in the data, they're just part of the UI.
您将数据库中的内容与您在 SQL 语句中表示该数据的方式混淆了。当数据库中的字符串包含特殊字符时\，您必须键入\\来表示该字符，因为\是 SQL 语法中的特殊字符。您必须在INSERT语句中执行此操作，但您也必须在REPLACE函数的参数中执行此操作。数据中实际上从来没有任何双斜线，它们只是 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.
为什么您认为需要将 SQL 表达式中的斜杠加倍？如果您正在输入查询，您应该在命令行中将斜杠加倍。如果您使用编程语言生成查询，最好的解决方案是使用准备好的语句；API 将负责正确的编码（准备好的语句通常使用二进制接口来处理原始数据）。如果出于某种原因，您需要通过构造字符串来执行查询，那么该语言应该有希望提供一个函数来转义字符串。例如，在 PHP 中，您将使用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.
但是你不能通过 SQL 本身来完成——如果你尝试将非转义字符串提供给 SQL，数据就会丢失并且无法重建它。
回答by RajKannan
You could use LIKE:
你可以使用LIKE：
SELECT NAME FROM T WHERE NAME LIKE '%\\%';
回答by Rohan Durve
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?
这是你想要的吗？
