97

I have an SQL column where the entries are strings. I need to display those entries after trimming the last two characters, e.g. if the entry is 199902345 it should output 1999023.

I tried looking into TRIM but looks like it offers to trim only if we know what are the last two characters. But in my case, I don't know what those last two numbers are and they just need to be discarded.

So, in short, what MySQL string operation enables to trim the last two characters of a string?

I must add that the length of the string is not fixed. It could be 9 characters, 11 characters or whatsoever.

Mostafa Norzade
  • 1,578
  • 5
  • 24
  • 40
Lucky Murari
  • 12,672
  • 5
  • 22
  • 43

4 Answers4

140

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:

SELECT col
     , 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:

SELECT col
     , TRIM(TRAILING '.php' FROM col)
-- index.php becomes index
-- index.php.php becomes index (!)
-- index.txt remains index.txt
Salman A
  • 262,204
  • 82
  • 430
  • 521
  • 2
    This Solution is very nice. But it requires to have direct access to the column. If the column is the result of some other Stringmanipulations this could be used instead: REVERSE(SUBSTR(REVERSE("some_string")), 2)) – Charliexyx Nov 18 '20 at 17:33
97

Why not use the LEFT(string, length) function instead of substring?

LEFT(col, char_length(col) - 2)

You can learn more about MySQL String functions here: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_left

Skully
  • 2,882
  • 3
  • 20
  • 31
Larz
  • 1,241
  • 10
  • 9
  • 1
    While the accepted answer is correct, I prefer this answer. I read some discussion about LEFT() being slightly more performant than SUBSTRING() but I'm not able to find a link right now. It would be great if you could add a link to the mysql documentation for the LEFT function. – anuragw Jan 25 '17 at 18:43
  • 1
    Much better solution than SUBSTRING() – Matthias Kleine Feb 02 '17 at 09:03
  • If the difference is putting `, 1` in the function call, I think having a whole new function to do that is just namespace pollution. `RIGHT` is slightly more useful in that it may save you from a `LENGTH()` calculation but still, any performance gain between these two is likely much less than the cost of calling `LENGTH()` separately. One or two add operations per call is the only difference between `LEFT` `RIGHT` and `SUBSTRING`. In SQL performance 2 native adds is nothing. – ebyrob Dec 21 '17 at 13:28
  • 1
    I had to use CHAR_LENGTH instead of LENGTH but this solution worked well. – Anthony Taylor Dec 01 '18 at 13:18
  • Wait, this is an incorrect answer! It should be CHAR_LENGTH!!! I've just corrected it – ACV Jan 19 '21 at 21:34
3

substring().

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • 3
    The length of the entries in the column is not known. I think, substring works better only for fixed length results. – Lucky Murari May 21 '11 at 09:55
0

You can use a LENGTH(that_string) minus the number of characters you want to remove in the SUBSTRING() select perhaps or use the TRIM() function.

Richard
  • 9
  • 2
  • 1
    If you can show an example of `TRIM()` working for all characters by index instead of removing all of a certain type of characters I'd be interested to see it. – ebyrob Dec 21 '17 at 13:32