MySQL - 替换列中的字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7734077/
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 Character in Columns
提问by TheCarver
Being a self-taught newbie, I created a large problem for myself. Before inserting data in to my database, I've been converting apostrophes (') in a string, to double quotes (""), instead of the required back-slash and apostrophe (\'), which MySQL actually requires.
作为一个自学的新手，我给自己制造了一个大问题。在将数据插入到我的数据库之前，我已经将字符串中的撇号 (') 转换为双引号 ("")，而不是 MySQL 实际需要的必需的反斜杠和撇号 (\')。
Before my table grows more than the 200,000 rows it already is, I thought it was best to rectify this issue immediately. So I did some research and found the SQL REPLACE function, which is great, but I'm now confused.
在我的表增长超过 200,000 行之前，我认为最好立即纠正这个问题。所以我做了一些研究，发现了 SQL REPLACE 函数，这很棒，但我现在很困惑。
In ASP, I was doing this:
在 ASP 中，我是这样做的：
str = Replace(str,"'","""")
If I look at my database in SQL Workbench, the symbol I converted is now a single quote ("), which has confused me a little. I understand why it changed from double to single, but I don't know which one I'm meant to be changing now.
如果我在 SQL Workbench 中查看我的数据库，我转换的符号现在是单引号 (")，这让我有点困惑。我明白为什么它从双引号变为单引号，但我不知道我是哪个我的意思是现在要改变。
To go through and rectify my problem using SQL REPLACE, do I now convert single quotes (") to back-slash and apostrophes (\') or do I convert double quotes ("") to back-slash and apostrophes (\')?
要使用 SQL REPLACE 解决并纠正我的问题，我现在是将单引号 (") 转换为反斜杠和撇号 (\') 还是将双引号 ("") 转换为反斜杠和撇号 (\') ?
For example, this:
例如，这个：
SQL = " SELECT REPLACE(myColumn,"""","\'") FROM myTable "
or this:
或这个：
SQL = " SELECT REPLACE(myColumn,""","\'") FROM myTable "
I hope I explained myself well, any suggestions gratefully received as always. Any queries about my question, please comment.
我希望我能很好地解释自己，任何建议一如既往地感激收到。对我的问题有任何疑问，请发表评论。
Many thanks
非常感谢
-- UPDATE --
- 更新 -
I have tried the following queries but still fail to change the ( " ) in the data:
我尝试了以下查询，但仍然无法更改数据中的 (")：
SELECT REPLACE(caption,'\"','\'') FROM photos WHERE photoID = 3371
SELECT REPLACE(caption,'"','\'') FROM photos WHERE photoID = 3371
SELECT REPLACE(caption,'""','\'') FROM photos WHERE photoID = 3371
Yet if I search:
然而，如果我搜索：
SELECT COUNT(*) FROM photos WHERE caption LIKE '%"%'
I get 16,150 rows.
我得到 16,150 行。
-- UPDATE 2 --
-- 更新 2 --
Well, I have created a 'workaround'. I managed to convert an entire column pretty quickly writing an ASP script, using this SQL:
好吧，我已经创建了一个“解决方法”。我设法使用以下 SQL 编写 ASP 脚本非常快速地转换了整个列：
SELECT photoID, caption FROM photos WHERE caption LIKE '%""%';
and then in ASP I did:
然后在 ASP 中我做了：
caption = Replace(caption,"""","\'")
But I would still like to know why I couldn't achieve that with SQL?
但我仍然想知道为什么我不能用 SQL 实现这一点？
回答by mellamokb
Just running the SELECTstatement will have no effect on the data. You have to use an UPDATEstatement with the REPLACEto make the change occur:
只运行SELECT语句不会对数据产生影响。您必须使用带有 的UPDATE语句REPLACE来进行更改：
UPDATE photos
SET caption = REPLACE(caption,'"','\'')
Here is a working sample: http://sqlize.com/7FjtEyeLAh
这是一个工作示例：http: //sqlize.com/7FjtEyeLAh
回答by Umar Adil
Replace below characters
替换以下字符
~ ! @ # $ % ^ & * ( ) _ +
` - =
{ } |
[ ] \
: "
; '
< > ?
, .
with this SQL
用这个 SQL
SELECT note as note_original,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(note, '\"', ''),
'.', ''),
'?', ''),
'`', ''),
'<', ''),
'=', ''),
'{', ''),
'}', ''),
'[', ''),
']', ''),
'|', ''),
'\'', ''),
':', ''),
';', ''),
'~', ''),
'!', ''),
'@', ''),
'#', ''),
'$', ''),
'%', ''),
'^', ''),
'&', ''),
'*', ''),
'_', ''),
'+', ''),
',', ''),
'/', ''),
'(', ''),
')', ''),
'-', ''),
'>', ''),
' ', '-'),
'--', '-') as note_changed FROM invheader
回答by Nathan
maybe I'd go by this.
也许我会通过这个。
SQL = SELECT REPLACE(myColumn, '""', '\'') FROM myTable
I used singlequotes because that's the one that registers string expressions in MySQL, or so I believe.
我使用单引号是因为那是在 MySQL 中注册字符串表达式的单引号，或者我相信。
Hope that helps.
希望有帮助。
回答by Esselans
If you have "something" and need 'something', use replace(col, "\"", "\'")and viceversa.
如果您有“某物”并且需要“某物”，请使用replace(col, "\"", "\'")，反之亦然。
