MySQL 查询以删除字符串中最后一个逗号之后的所有字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17421448/
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
query to remove all characters after last comma in string
提问by beNerd
i have a mysql table with this sort of data
我有一个包含这种数据的 mysql 表
TACOMA, Washington, 98477
Now i have thousands of such rows. I want the data to be manipulated in such a manner that it appears like:
现在我有成千上万的这样的行。我希望以如下方式操作数据：
TACOMA, Washington
Is it possible though mysql or do i have to manually do it.
是否可以通过 mysql 或我必须手动执行它。
回答by Stephan
You can use :
您可以使用 ：
SELECT SUBSTRING_INDEX('TACOMA, Washington, 98477', ',', 2)
You can read more here.
您可以在此处阅读更多内容。
And the update statement :
和更新声明：
UPDATE my_table
SET my_col = SUBSTRING_INDEX(my_col, ',', 2)
Where you need to replace my_tablewith your table name and my_colwith the column you need to be updated.
您需要my_table用表名和my_col需要更新的列替换的位置。
回答by Kickstart
Possibly this way. Count the number of commas (by checking the length against the length with all the commas removed) and then use SUBSTRING_INDEX to get the string up to the number of commas:-
可能是这样。计算逗号的数量（通过检查长度与删除所有逗号的长度），然后使用 SUBSTRING_INDEX 使字符串达到逗号的数量：-
SELECT SUBSTRING_INDEX(col, ',', LENGTH(col) - LENGTH(REPLACE(col, ',', '')))
FROM SomeTable
回答by chetan
substring_index(col, ',',-1)
will give the string from last index of comma to end of string
将给出从逗号的最后一个索引到字符串结尾的字符串
replace(col,concat(',',substring_index(col, ',',-1)),'')
