esn studio

UPDATE `tablename`
SET `columnname` =
TRIM(LEADING  'THE CHARACTER YOU WANT TO REMOVE, eg: A'  FROM `columnname`);

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

mysql> SELECT TRIM('  bar   ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'

This function is multi-byte safe.

Source: MySQL 5.0 Reference Manual

Referral text link:

This entry was posted on Monday, February 26th, 2007 at 9:22 am and is filed under Snippets. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.