SQL查询从特定列中的每个字段中删除某些文本？

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_name column?

是否有任何我可以运行的SQL查询允许我从file_name列中的每个字段中删除文件exentision？

3 个解决方案

#1

You can use the REPLACE() function in native MySQL to do a simple string replacement.

您可以在本机MySQL中使用REPLACE（）函数来执行简单的字符串替换。

UPDATE tbl SET file_name = REPLACE(file_name, '.jpg', '');
UPDATE tbl SET file_name = REPLACE(file_name, '.rar', '');

#2

This should work:

这应该工作：

UPDATE MyTable
SET file_name = SUBSTRING(file_name,1, CHAR_LENGTH(file_name)-4)

#3

This will strip off the final extension, if any, from file_name each 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);