Separate the last two characters of a column in MySQL

Question

I have an SQL column where the records are rows. I need to display these entries after trimming the last two characters, for example, if the entry 199902345 should output 1999023 .

I tried to peek into TRIM, but it looks like it offers trimming only if we know what the last two characters are. But in my case, I don’t know what the last two numbers are, and they just need to be thrown away.

So, briefly, what string operation of MySQL allows to trim the last two characters of a string?

I must add that the string length is not fixed. It can be 9 characters, 11 characters or something else.

Answer 1

To select all characters except the last n from the string (or, in other words, remove the last n characters from the string); use the SUBSTRING and CHAR_LENGTH together:

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

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

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

Answer 2

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

 LEFT(col,length(col)-2) 

you can visit https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_left to learn more about Mysql string functions.

Answer 3

substring ().

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

Answer 4

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