Menu
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.
818
To cut only the first character, use the substr() function with UPDATE command. The syntax is as follows. UPDATE yourTableName set yourColumnName=substr(yourColumnName,2); To understand the above syntax, let us first create a table.
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.
Syntax: SELECT SUBSTRING(column_name,1,length(column_name)-4) FROM table_name; Example : Remove the last 4 characters from the NAME field.
SQL Server TRIM() Function The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string.
Check this website
SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); which in your case would be ',' instead of 'xyz'
199
@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!
44
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
