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

mysql database trim
codacopia picture codacopia · Jul 16, 2011 · Viewed 29.9k times · Source

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?

Answer

Shef picture Shef · Jul 16, 2011

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.

Related questions

'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More
How do I quickly rename a MySQL database (change schema name)?

The MySQL manual at MySQL covers this. Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does …

Read More
MySQL Error: : 'Access denied for user 'root'@'localhost'

$ ./mysqladmin -u root -p 'redacted' Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' How can I fix this?

Read More