PHP snippets
PHP, MySQL, Javascript, HTML and CSS code examples.

MySQL remove trailing characters from a field

Posted: February 26th, 2011 | Author: admin | Filed under: MySQL | No Comments »

Sometimes you want to trim trailing characters (other than spaces) from a field, like an URL.  MySQL TRIM function removes only spaces from a string. So for example if we have an url http://www.mysite.com/test/ and you want to remove the trailing / this is how is done, using TRAILING keyword:

1
2
3
SELECT TRIM(TRAILING '/' FROM myfield ) FROM mytable;

#the result will be http://www.mysite.com/test

Also you can use TRIM function to remove starting characters in a string using LEADING keyword instead of TRAILING

1
2
3
4
SELECT TRIM(LEADING 'http://' FROM myfield ) FROM mytable;

#this will remove http:// from beginning of the string,
#the result will be: www.mysite.com/test/

And if you want to remove both leading and trailing characters, you can use BOTH keyword

1
2
3
4
SELECT TRIM(LEADING 'aa' FROM 'aabcdaa');

#this will remove 'aa'  from beginning and end of the string,
#the result will be: bcd

And at last, if you have thousands of records and you want to remove last characters from all records, you can do this very easy using TRIM function

1
UPDATE `mytable` SET myfield=TRIM(TRAILING '/' FROM myfield );

Leave a Reply