Remove last two characters of a string column in MySQL

To select all characters except the last n from a string (or put another way, remove last n characters from a string); use the SUBSTRING and CHAR_LENGTH functions together:

     , SUBSTRING(col FROM 1 FOR CHAR_LENGTH(col) - 2) AS col_trimmed -- ANSI Syntax
     , SUBSTRING(col,     1,    CHAR_LENGTH(col) - 2) AS col_trimmed -- MySQL Syntax
FROM tbl

To remove a specific substring from the end of string, use the TRIM function:

     , TRIM(TRAILING '.php' FROM col)
-- index.php becomes index
-- index.php.php becomes index (!)
-- index.txt remains index.txt

Leave a Comment