31

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?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
codacopia
  • 2,369
  • 9
  • 39
  • 58

4 Answers4

76

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.

Shef
  • 44,808
  • 15
  • 79
  • 90
  • 1
    The other methods worked for changing the values, but your answer actually updates the table which is what I needed. Thanks :) – codacopia Jul 16 '11 at 17:27
13

Just change that to 

SELECT TRIM(BOTH '"' FROM title) AS trimmed_title FROM `example_table`
Sam Dufel
  • 17,560
  • 3
  • 48
  • 51
8

This solved my problem

UPDATE table_name SET column_name = REPLACE(column_name,'"','')
Janie
  • 638
  • 9
  • 26
5

this works for me

select trim(both '"' from column_name) from table_name;