查找并用字段中的反斜杠替换字符串的特定部分 [英] Find and replace a specific part of a string with a backslash in a field

问题描述

例如这是myTable:

| i | data    |
+---+---------+
| 1 | d\one   |
| 2 | d\two   |
| 3 | d\three |

但我想把它改成:

| i | data  |
+---+-------+
| 1 | one   |
| 2 | two   |
| 3 | three |

我知道如何在字段中查找带有反斜杠的字符串的特定部分:

I know how to find a specific part of a string with a backslash in a field:

SELECT * FROM myTable WHERE data LIKE 'd%\%'

我知道如何找到 &将字段替换为值，只有此查询不起作用:

I know how to find & replace a field a value, only this query won't work:

UPDATE myTable SET data=REPLACE(data,'d\','') WHERE data LIKE 'd%\%'

推荐答案

我希望你应该在 \\\ 中放置三斜线 \\\ 而不是双斜线 \\>喜欢条款.正如我尝试过的，我无法从使用 \\

I hope you should put triple slash \\\ instead of double slash \\ in Like Clause. As I have tried I am unable to pick data from using \\

SELECT * FROM mytable WHERE data LIKE 'd\\%';
Empty set (0.00 sec)

mysql> SELECT * FROM mytable WHERE data LIKE 'd\\\%';
+---------+
| data    |
+---------+
| d\one   |
| d\two   |
| d\three |
+---------+
3 rows in set (0.00 sec)

mysql> SELECT REPLACE(data,'d\\','') FROM mytable WHERE data LIKE 'd\\\%';
+------------------------+
| REPLACE(data,'d\\','') |
+------------------------+
| one                    |
| two                    |
| three                  |
+------------------------+
3 rows in set (0.00 sec)

mysql> UPDATE mytable SET data =  REPLACE(data,'d\\','') WHERE data LIKE 'd\\\%';
Query OK, 3 rows affected (0.06 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM mytable;
+-------+
| data  |
+-------+
| one   |
| two   |
| three |
+-------+
3 rows in set (0.00 sec)

这篇关于查找并用字段中的反斜杠替换字符串的特定部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！