Articles Cookbook
Back to list
MySQL

How to Remove Unwanted Leading Characters from a String in MySQL

Database:

Operators:

TRIM LEADING

Problem:

You’d like to remove a sequence of characters at the beginning of a string.

Example:

Our database has a table named product with data in three columns: id, name, and model.

idnamemodel
1watchLx0A123
2smartwatchLx0W34
3clock photoLx0G100

Let’s trim the model code of each new product, removing the unnecessary characters (Lx0) at its beginning.

Solution 1:

We’ll use the TRIM() function. Here’s the query you would write:

SELECT name, 
  TRIM(LEADING 'Lx0' FROM model ) AS new_model_code
FROM product;

Here’s the result:

namenew_model_code
watchA123
smartwatchW34
clock photoG100

Discussion:

Use the TRIM() function with the LEADING keyword to remove characters at the beginning of a string. TRIM() allows you to remove specific character(s) or space(s) from the beginning, end, or both ends of a string. This function takes the following arguments:

  • An optional keyword that specifies the end(s) to trim. By default, it’s BOTH, but you can define LEADING (remove from beginning) or TRAILING (remove from end).
  • A string defining the character(s)/space(s) you want to remove from the string (in our example, the sequence ‘Lx0’).
  • The FROM keyword.
  • The string/column name to be trimmed (in our example, the model column).

In our example, this looks like:

TRIM(LEADING 'Lx0' FROM model)

Recommended courses:

Recommended articles:

See also:

go to top