删除MySQL中列的最后两个字符 [英] Strip last two characters of a column in MySQL
问题描述
我有一个SQL列,其中的条目是字符串.我需要在剪掉最后两个字符后显示这些条目,例如如果条目为199902345
,则应输出1999023
.
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
.
我尝试研究TRIM,但看起来只有在我们知道最后两个字符是什么的情况下,它才能提供修剪功能.但就我而言,我不知道最后两个数字是什么,只需要丢弃它们即可.
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.
那么,简而言之,什么MySQL字符串操作可以修剪字符串的最后两个字符?
So, in short, what MySQL string operation enables to trim the last two characters of a string?
我必须补充一点,字符串的长度不是固定的.可以是9个字符,也可以是11个字符.
I must add that the length of the string is not fixed. It could be 9 characters, 11 characters or whatsoever.
推荐答案
要从字符串中选择除最后一个 n 以外的所有字符(或换句话说,删除最后一个 n >字符串中的字符);使用 SUBSTRING
和 CHAR_LENGTH
一起起作用:
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.txt remains index.txt
这篇关于删除MySQL中列的最后两个字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!