Menu
I have a list of keywords like below and some keyword lists in the database have a leading or trailing ,
keyword,keyword,keyword,keyword,
How do I trim the leading and/or trailing , using mysql?
I have tried using trim but can't seem to get it to work on an UPDATE, which is what I'm trying to achieve.
348
To remove all characters after the last comma in the string, you can use SUBSTRING_INDEX(). If you do not know the location of the last comma, then you need to find the last comma dynamically using LENGTH().
Use the TRIM() function with the LEADING keyword to remove characters at the beginning of a string. TRIM() allows you to remove specific character(s) or space(s) from the beginning, end, or both ends of a string. This function takes the following arguments: An optional keyword that specifies the end(s) to trim.
SELECT TRIM(BOTH ',' FROM ',,,demo, ,xxxx,,,yyy,,,'); SELECT REPLACE(TRIM(TRIM(',' FROM ',,,demo, ,xxxx,,,yyy,,,')), ',,', ',');
The TRIM() function returns a string that has unwanted characters removed. Note that to remove the leading spaces from a string, you use the LTRIM() function. And to remove trailing spaces from a string, you use the RTRIM() function.
TRIM(BOTH ',' FROM ',keyword,keyword,')
The above would return 'keyword,keyword'.
BOTH can be replaced with either LEADING or TRAILING if you just want to trim on one side of the string.
Documentation
There shouldn't be any problem using TRIM in an UPDATE query, but without the query in question we cannot offer any specific help. But TRIM is just like any other function available, it takes a set of parameters and returns a value.
Sample post-insert query to fix fields with trailing ','
UPDATE `table_name` SET `keywords` = TRIM(TRAILING ',' FROM `keywords`);
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
