MySQL SQL查询从特定列中的每个字段中删除某些文本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9049259/
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
SQL query to remove certain text from each field in a specific column?
提问by Josh Foskett
I recently recoded one of my sites, and the database structure is a little bit different.
我最近重新编码了我的一个网站，数据库结构有点不同。
I'm trying to convert the following:
我正在尝试转换以下内容：
*----*----------------------------*
| id | file_name |
*----*----------------------------*
| 1 | 1288044935741310953434.jpg |
*----*----------------------------*
| 2 | 1288044935741310352357.rar |
*----*----------------------------*
Into the following:
进入以下内容：
*----*----------------------------*
| id | file_name |
*----*----------------------------*
| 1 | 1288044935741310953434 |
*----*----------------------------*
| 2 | 1288044935741310352357 |
*----*----------------------------*
I know that I could do a foreach loop with PHP, and explode the file extension off the end, and update each row that way, but that seems like way too many queries for the task.
我知道我可以用 PHP 做一个 foreach 循环，并在最后爆炸文件扩展名，并以这种方式更新每一行，但这似乎对任务的查询太多了。
Is there any SQL query that I could run that would allow me to remove the file exentision from each field in the file_namecolumn?
是否有任何我可以运行的 SQL 查询可以让我从file_name列中的每个字段中删除文件扩展名？
回答by Michael Berkowski
You can use the REPLACE()functionin native MySQL to do a simple string replacement.
您可以使用本REPLACE()机 MySQL 中的函数进行简单的字符串替换。
UPDATE tbl SET file_name = REPLACE(file_name, '.jpg', '');
UPDATE tbl SET file_name = REPLACE(file_name, '.rar', '');
回答by Bassam Mehanni
This should work:
这应该有效：
UPDATE MyTable
SET file_name = SUBSTRING(file_name,1, CHAR_LENGTH(file_name)-4)
回答by pilcrow
This will strip off the final extension, if any, from file_nameeach time it is run. It is agnostic with respect to extension (so you can have ".foo" some day) and won't harm extensionless records.
这将在file_name每次运行时去除最终扩展（如果有）。它与扩展名无关（所以有一天你可以拥有“.foo”）并且不会损害无扩展名的记录。
UPDATE tbl
SET file_name = TRIM(TRAILING CONCAT('.', SUBSTRING_INDEX(file_name, '.', -1) FROM file_name);
回答by Ali hasan
You can use SUBSTRING_INDEXfunction
您可以使用SUBSTRING_INDEX函数
SUBSTRING_INDEX(str,delim,count)
SUBSTRING_INDEX(str,delim,count)
Where str is the string, delim is the delimiter (from which you want a substring to the left or right of), and count specifies which delimiter (in the event there are multiple occurrences of the delimiter in the string)
其中 str 是字符串， delim 是分隔符（您想要在其左侧或右侧的子字符串），并且 count 指定哪个分隔符（如果字符串中出现多次分隔符）
Example:
例子：
UPDATE table SET file_name = SUBSTRING_INDEX(file_name , '.' , 1);
