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

Home»Software»Databases»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.

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.Connect with LK through Tech Journey on Facebook, Twitter or Google+.
  • Susan Douthart

    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!

  • sarang

    how to remove " this combine charector using only string function please its urgent

  • Darren Fauth

    Nice explanation. Thanks. This page has become my go to solution for updating a ton of data that I am working with.

  • HomeList

    Thanks for this quick tutorial =)

  • sreekanth

    i want to update the column only first 4char's only. Remaining will be there.

    if any body knows please let me know

  • Estopero

    Hi Susan,

    I know that this will not very usefull for you right now 2 years laters but for visiters looking for a solution… 🙂

    UPDATE SET = INSERT(, , , '');

    For your query

    UPDATE SET = INSERT(, 1, 1, '');

Pin It on Pinterest

Share This

Share This

Share this post with your friends!