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
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 ''