Menu
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?
337
Try this: str_replace('"', "", $string); str_replace("'", "", $string); Otherwise, go for some regex, this will work for html quotes for example: preg_replace("/<!
You can easily escape single quotes, double quotes, apostrophe, backticks and other special characters by adding a backslash (\) before that character.
TRIM() Function in MySQL BOTH | LEADING | TRAILING : LEADING, TRAILING, or BOTH option to explicitly instruct the TRIM() function to remove leading, trailing, or both leading and trailing unwanted characters from a string . By default, the TRIM() function uses the BOTH option.
MySQL a-z in Telugu You can remove special characters from a database field using REPLACE() function. The special characters are double quotes (“ “), Number sign (#), dollar sign($), percent (%) etc.
Let’s take some examples of using the MySQL TRIM () function. The following statement uses the TRIM () function to remove both leading and trailing spaces from a string.
MySQL provides a very useful string function named TRIM () to help you clean up the data. The following illustrates the syntax of the TRIM () function. The TRIM function provides a number of options. You can use the LEADING, TRAILING, or BOTH option to explicitly instruct the TRIM () function to remove leading, trailing, ...
Code language: SQL (Structured Query Language) (sql) The TRIM function provides a number of options. You can use the LEADING, TRAILING, or BOTH option to explicitly instruct the TRIM () function to remove leading, trailing, or both leading and trailing unwanted characters from a string. By default, the TRIM () function uses the BOTH option.
One of the most important tasks in data cleansing is to remove the unwanted leading and trailing characters. MySQL provides a very useful string function named TRIM () to help you clean up the data.
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.
95
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
