MySQL - 替换列中的字符 [英] MySQL - Replace Character in Columns

问题描述

作为一个自学的新手，我给自己制造了一个大问题.在将数据插入到我的数据库之前，我已经将字符串中的撇号 (') 转换为双引号 ("")，而不是 MySQL 实际需要的必需的反斜杠和撇号 (\').

在我的表增长超过 200,000 行之前，我认为最好立即纠正这个问题.所以我做了一些研究，发现了 SQL REPLACE 函数，这很棒，但我现在很困惑.

在 ASP 中，我是这样做的:

str = Replace(str,"'","""")

如果我在 SQL Workbench 中查看我的数据库，我转换的符号现在是单引号 (")，这让我有点困惑.我明白为什么它从双引号变为单引号，但我不知道是哪个一个我现在打算改变的.

要使用 SQL REPLACE 解决并纠正我的问题，我现在是将单引号 (") 转换为反斜杠和撇号 (\')，还是将双引号 ("") 转换为反斜杠和撇号 (\')?

例如:

SQL = " SELECT REPLACE(myColumn,"""","\'") FROM myTable "

或者这个:

SQL = " SELECT REPLACE(myColumn,""","\'") FROM myTable "

我希望我能很好地解释自己，任何建议一如既往地感激收到.对我的问题有任何疑问，请发表评论.

非常感谢

-- 更新 --

我尝试了以下查询，但仍然无法更改数据中的 ("):

SELECT REPLACE(caption,'\"','\'') FROM photos WHERE photoID = 3371SELECT REPLACE(caption,'"','\'') 从照片 WHERE photoID = 3371SELECT REPLACE(caption,'""','\'') 从照片中选择照片 ID = 3371

但是如果我搜索:

SELECT COUNT(*) FROM photos WHERE caption LIKE '%"%'

我得到 16,150 行.

-- 更新 2 --

好吧，我已经创建了一个解决方法".我设法使用此 SQL 编写 ASP 脚本非常快速地转换了整个列:

SELECT photoID, caption FROM photos WHERE caption LIKE '%""%';

然后在 ASP 中我做了:

caption = Replace(caption,"""","\'")

但我仍然想知道为什么我不能用 SQL 实现这一点?

解决方案

只运行 SELECT 语句对数据没有影响.您必须使用带有 REPLACE 的 UPDATE 语句来进行更改:

更新照片SET caption = REPLACE(caption,'"','\'')

这是一个工作示例:http://sqlize.com/7FjtEyeLAh

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.

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.

In ASP, I was doing this:

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.

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 (\')?

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.

-- UPDATE 2 --

Well, I have created a 'workaround'. I managed to convert an entire column pretty quickly writing an ASP script, using this SQL:

SELECT photoID, caption FROM photos WHERE caption LIKE '%""%';

and then in ASP I did:

caption = Replace(caption,"""","\'")

But I would still like to know why I couldn't achieve that with SQL?

解决方案

Just running the SELECT statement will have no effect on the data. You have to use an UPDATE statement with the REPLACE to make the change occur:

UPDATE photos
   SET caption = REPLACE(caption,'"','\'')

Here is a working sample: http://sqlize.com/7FjtEyeLAh

这篇关于MySQL - 替换列中的字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！