SQL tip for removing the first or last character from a column

It happens that in a database, a field has an extra character at the beginning or at the end of a column. It is then convenient to exclude or delete this extra character. This article presents several SQL queries to exclude the first or the last character contained in a column of a database.

Exclude first character

To exclude the first character, use the SUBSTR () function which allows you to extract part of a string:

SELECT SUBSTR(nom_colonne, 2) AS colonne_sans_first
FROM table
WHERE condition

To know : the function is called differently depending on the database management system:

• MySQL: SUBSTR (), SUBSTRING ()
• PostgreSQL: SUBSTR (), SUBSTRING ()
• Oracle: SUBSTR ()
• SQL Server: SUBSTRING ()

Delete the first character

It is also possible to delete the first character once and for all by updating with UPDATE. To do this, you can use the following SQL query:

UPDATE table
SET nom_colonne = SUBSTR(nom_colonne, 2)
WHERE condition

This query once again uses the SUBSTR () function to extract the entire string except the first character.

To know : it is possible to delete more than one character at the start of the string by modifying the value 2.

Exclude last character

It is also possible to remove the last character of a string in the result of an SQL query. It’s a bit complicated, but the query looks like this:

SELECT nom_colonne, SUBSTR(nom_colonne, 1, CHAR_LENGTH(nom_colonne) - 1) AS colonne_sans_last
FROM table
WHERE condition

In detailing, we notice that it is not that hard. Just truncate using SUBSTRING from 1 to the maximum size minus 1 character.

To know : the CHAR_LENGTH () function can be called in several different ways depending on the database management system:

• MySQL: CHAR_LENGTH or CHARACTER_LENGTH ()
• PostgreSQL: CHAR_LENGTH or CHARACTER_LENGTH ()
• Oracle: CHAR_LENGTH or CHARACTER_LENGTH ()
• SQL Server: LEN ()

Delete the last character

To update a table by removing the last character from a string, just use the same trick as before but in an UPDATE query:

UPDATE table
SET nom_colonne = SUBSTR(nom_colonne, 1, CHAR_LENGTH(nom_colonne) - 1)
WHERE condition

Last word

These example queries can be useful when you realize, for example, that a field is stored in parentheses and that these parentheses can be removed.

Tip: However, you must ensure that the updated column has at least 1 character. Therefore, you should remember to use the WHERE condition to exclude records in which the string is empty with a condition that looks like this:

WHERE nom_colonne  ''