在 MySQL 中删除列的最后两个字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6080662/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Strip last two characters of a column in MySQL
提问by Lucky Murari
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 199902345it should output 1999023.
我有一个 SQL 列，其中条目是字符串。我需要在修剪最后两个字符后显示这些条目，例如，如果条目是199902345它应该输出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.
我尝试查看 TRIM，但看起来它仅在我们知道最后两个字符是什么时才提供修剪。但就我而言，我不知道最后两个数字是什么，只需将它们丢弃即可。
So, in short, what MySQL string operation enables to trim the last two characters of a string?
那么，简而言之，什么 MySQL 字符串操作可以修剪字符串的最后两个字符？
I must add that the length of the string is not fixed. It could be 9 characters, 11 characters or whatsoever.
我必须补充一点，字符串的长度不是固定的。它可以是 9 个字符、11 个字符或其他任何字符。
回答by Salman A
To select all characters except the last nfrom a string (or put another way, remove last ncharacters from a string); use the SUBSTRINGand CHAR_LENGTHfunctions together:
从字符串中选择除最后n 个字符之外的所有字符（或者换句话说，从字符串中删除最后n 个字符）；一起使用SUBSTRING和CHAR_LENGTH函数：
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 TRIMfunction:
要从字符串末尾删除特定子字符串，请使用以下TRIM函数：
SELECT col
, TRIM(TRAILING '.php' FROM col)
-- index.php becomes index
-- index.txt remains index.txt
回答by Larz
Why not using LEFT(string, length) function instead of substring.
为什么不使用 LEFT(string, length) 函数而不是子字符串。
LEFT(col,length(col)-2)
you can visit here https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_leftto know more about Mysql String Functions.
你可以访问这里https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_left了解更多关于 Mysql 字符串函数的信息。
回答by Denis de Bernardy
回答by Richard
You can use a LENGTH(that_string)minus the number of charactersyou want to remove in the SUBSTRING()select perhaps or use the TRIM()function.
您可以在选择中使用要删除的LENGTH(that_string)减号或使用该功能。number of charactersSUBSTRING()TRIM()
