Menu
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.
959
Below is the syntax for the SUBSTRING() function to delete the last N characters from the field. Syntax: SELECT SUBSTRING(column_name,1,length(column_name)-N) FROM table_name; Example: Delete the last 2 characters from the FIRSTNAME column from the geeksforgeeks table.
To get the first n characters of string with MySQL, use LEFT(). To get the last n char of string, the RIGHT() method is used in MySQL.
The TRIM() function returns a string that has unwanted characters removed. Note that to remove the leading spaces from a string, you use the LTRIM() function. And to remove trailing spaces from a string, you use the RTRIM() function.
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 , /* 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 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
