CODEWITHSUNDEEP
mysqldatabasetrim
codacopia
codacopia

Reputation: 2501

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?

Upvotes: 37

Views: 47917

Answers (4)

Janie
Janie

Reputation: 646

This solved my problem

UPDATE table_name SET column_name = REPLACE(column_name,'"','')

Upvotes: 8

user747858
user747858

Reputation:

this works for me

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

Upvotes: 6

Shef
Shef

Reputation: 45589

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.

Upvotes: 84

Sam Dufel
Sam Dufel

Reputation: 17598

Just change that to 

SELECT TRIM(BOTH '"' FROM title) AS trimmed_title FROM `example_table`

Upvotes: 14

Related Questions