How to Remove Leading Characters from a String in MySQL

Problem:

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

Example:

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

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:

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).