Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

database - How Do I Trim Leading and Trailing Quote from MySQL Row?

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:

"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:

SELECT * FROM `example_table` TRIM(LEADING '"' FROM "title")

This is the error from MySQL when I run the call:

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

How do I go about getting rid of all the trailing and leading quotation marks from the row?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Try:

UPDATE `example_table` 
   SET `title` = TRIM(BOTH '"' FROM `title`)

This query will updated your example_table to remove leading and trailing double quotes from the value of the title column.

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...