CODEWITHSUNDEEP
mysql
Josh Foskett
Josh Foskett

Reputation: 4121

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

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?

Upvotes: 19

Views: 53482

Answers (4)

Ali Hasan
Ali Hasan

Reputation: 673

You can use SUBSTRING_INDEX function

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)

Example: 

UPDATE table SET file_name = SUBSTRING_INDEX(file_name , '.' , 1);

Upvotes: 0

pilcrow
pilcrow

Reputation: 58534

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.

UPDATE tbl
   SET file_name =  TRIM(TRAILING CONCAT('.', SUBSTRING_INDEX(file_name, '.', -1) FROM file_name);

Upvotes: 1

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

This should work:

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

Upvotes: 3

Michael Berkowski
Michael Berkowski

Reputation: 270609

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', '');

Upvotes: 54

Related Questions