Remove or Trim First or Last Few Characters in MySQL Database with SQL

Another useful string function in MySQL database is TRIM() which will return a text string after removing the matching leading or trailing characters, also known as prefixes or suffixes. It’s been described by MySQL reference as function that 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.

Syntax of TRIM():

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

For example:

SELECT TRIM(’ bar ‘);

will return ‘bar’ (spaces dropped).

SELECT TRIM(LEADING ‘x’ FROM ‘xxxbarxxx’);

will return ‘barxxx’ (only leading x characters is removed).

SELECT TRIM(BOTH ‘x’ FROM ‘xxxbarxxx’);

will return ‘bar’ (leading and trailing xs is dropped).

SELECT TRIM(TRAILING ‘xyz’ FROM ‘barxxyz’);

will return ‘barx’ (trailing xyz is cleared).

This function is multi-byte safe. And it can also be used with other SQL command such as UPDATE to perform modification update directly on database table data with SQL statements using tool such as phpMyAdmin.


This entry was posted on Monday, April 23rd, 2007 at 5:02 pm and is filed under Databases. 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.

One Response to “Remove or Trim First or Last Few Characters in MySQL Database with SQL”

  1. Susan Douthart
    October 29th, 2009 03:06
    1

    Hi! I have a column of numbers that are in ascending order:

    ‘1005849011050
    ‘1005849011051
    ‘1005849011052
    ‘1005849011053
    ‘1005849011054
    ‘1005849011055
    ‘1005849011056
    ‘1005849011057

    They are all led by an apostrophe that I’d like to remove. How would I use the trim statement to do this? THANKS!

Leave a Reply

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe to comments feature has been disabled. To receive notification of latest comments posted, subscribe to My Digital Life Comments RSS feed or register to receive new comments in daily email digest.
« How to Find and Replace Text in MySQL Database using SQL
Search for Genuine (and Free) Windows Product Keys Online with Google »

Custom Search

New Articles

Incoming Search Terms for the Article

sql trim characters - mysql trim text - SQL remove characters - trim characters SQL - mysql remove last character - sql remove char - mysql trim characters - mysql remove first char - mysql remove characters - mysql trim - trim characters in sql - sql remove leading characters - mysql strip characters - sql trim char - sql remove first character from string - mysql remove character - sql remove character - TRIM CHAR SQL - mysql select trim - mysql trim string - pl sql remove last character - sql removing characters from string - mysql remove character from string - remove characters sql - remove character sql - remove first character sql - sql to remove characters - SQL trim to remove a letter - mysql remove first character - sql strip characters - trim characters before sql - trimming characters in sql - sql remove last digit - SELECT TRIM mysql UPDATE - how to delete first character in sql server 2005 - mysql replace last character in string - mysql trim last character - remove string sql - remove last 3 characters sql - remove last character from string in sql - sql remove characters from string - sql server trim first char - sql query remove first character - trim first character sql - mysql select last character - MYSQL REMOVE CHAR - sql remove first char of string - sql remove character from string - sql command remove characters - sql trim command -