如何从 MySQL 行中修剪前导和尾随引号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6718713/
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
How Do I Trim Leading and Trailing Quote from MySQL Row?
提问by codacopia
I have a MySQL table that I have imported from a CSV file. In this process, a bunch of the entries have quote marks leading and trailing the entry of several data rows. For example the the table 'example_table' I have a row called 'title.' Some of these titles are written as:
我有一个从 CSV 文件导入的 MySQL 表。在这个过程中，一堆条目在几个数据行的条目前面和后面都有引号。例如表'example_table' 我有一行叫做'title'。其中一些标题是这样写的：
"title1"
"title2"
"title3"
and some are written without the quote marks:
有些是不带引号的：
title4
title5
title6
I have tried a variety of SQL calls to trim the row but I keep getting errors. Here is my sql call:
我尝试了各种 SQL 调用来修剪该行，但我不断收到错误消息。这是我的 sql 调用：
SELECT * FROM `example_table` TRIM(LEADING '"' FROM "title")
This is the error from MySQL when I run the call:
这是我运行调用时来自 MySQL 的错误：
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
1064 - 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册以了解要使用的正确语法
How do I go about getting rid of all the trailing and leading quotation marks from the row?
我如何摆脱行中的所有尾随和前导引号？
回答by Shef
Try:
尝试：
UPDATE `example_table`
SET `title` = TRIM(BOTH '"' FROM `title`)
This query will updated your example_tableto remove leading and trailing double quotes from the value of the titlecolumn.
此查询将更新您example_table以从title列的值中删除前导和尾随双引号。
If you don't want to update the table, but want to fetch the rows with double quotes removed, then use @Sam Dufel's answer.
如果您不想更新表，但想获取删除双引号的行，请使用@Sam Dufel 的答案。
回答by Janie
This solved my problem
这解决了我的问题
UPDATE table_name SET column_name = REPLACE(column_name,'"','')
回答by Sam Dufel
Just change that to
只需将其更改为
SELECT TRIM(BOTH '"' FROM title) AS trimmed_title FROM `example_table`
回答by Sam Dufel
this works for me
这对我有用
select trim(both '"' from column_name) from table_name;
