mysql: How can I remove character at start or end

问题:

I have field that contains a comma-separated list. Some old records have a comma at the beginning or end of the field. I need to remove these extra commas.

Example:

,the,their,then to the,their,then

or

the,their,then, to the,their,then

EDIT: I am looking for an UPDATE statement. I need to change the records.

回答1:

Check this website

SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');

which in your case would be ',' instead of 'xyz'

回答2:

@codingbiz, Thank you for the website link:

Quick examples:

SELECT TRIM(BOTH ',' FROM fieldname) from tablename

SELECT TRIM(LEADING ',' FROM fieldname) from tablename

SELECT TRIM(TRAILING ',' FROM fieldname) from tablename

Thanks!

回答3:

The question was how to remove the leading and trailing characters I will show you an example with an update query.

UPDATE your_table_name
SET your_record_name = TRIM(BOTH ',' FROM your_record_name)

回答4:

You are looking for the TRIM function.