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

问题描述

作为一个自学的新手，我为自己创造了一个大问题。在将数据插入到我的数据库之前，我已经将字符串中的撇号（'）转换为双引号（），而不是MySQL实际需要的反斜杠和撇号（\'）。 / p>

在我的表增长超过20万行之前，我已经是最好立即纠正这个问题。所以我做了一些研究，发现了SQL REPLACE函数，这是伟大的，但我现在困惑。

在ASP，我这样做：

  str = Replace（str，'，）
  

如果我在SQL Workbench中查看我的数据库，我转换的符号现在是一个单引号（），它让我有点困惑我明白为什么它改变

要使用SQL REPLACE来解决我的问题，现在我现在将单引号（）转换为反斜线和撇号（\'）或将双引号（）转换为反斜线和撇号（\'）？

例如：

  SQL =SELECT REPLACE（myColumn，， ）FROM myTable
  

或：

  SQL =SELECT REPLACE（myColumn，，\'）FROM myTable
  

我希望我解释自己，任何建议感激地接受一如既往。

$ b

非常感谢

- UPDATE -

我尝试了以下查询，但仍无法更改数据中的（）：

  SELECT REPLACE（caption，'\''，'\'）FROM photos WHERE photoID = 3371 
 SELECT REPLACE（caption，'''，'\ ''）FROM photos WHERE photoID = 3371 
 SELECT REPLACE（caption，''，'\''）from photos WHERE photoID = 3371 
  

但如果我搜索：

  WHERE caption LIKE'％％'
  

我获得16,150行。

- UPDATE 2 -

好吧，我创建了一个解决方法。我设法使用这个SQL很快地转换整个列：ASP

  SELECT photoID，caption from photos WHERE caption喜欢 '％％'; 
  

然后在ASP中：

  caption = Replace（caption，，\'）
  

b $ b

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

解决方案

SELECT 语句对数据没有影响。您必须使用 UPDATE 语句与 REPLACE 进行更改：

 更新照片
 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屋！