SQL query to remove certain text from each field in a specific column?

Question

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.

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?

Answer 1

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

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

Answer 2

This should work:

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