CODEWITHSUNDEEP
mysqlsqlsql-update
Juriy
Juriy

Reputation: 5121

MySQL change the file extension in the text column

A simple question for MySQL-pro. I've got a table that has a field with the filename in it (just the filename, no extra text). I need to change all file extension from ".png" to ".jpg", I know there's a way to do it with just query and no scripting in the programming language like PHP or Java.

Just in case, dropping "show create table" output:

CREATE TABLE `photos` (
  `id` bigint(20) NOT NULL,
  `owner_id` int(11) DEFAULT NULL,
  `photo_name` varchar(255) DEFAULT NULL,
  `comment` text,
  `normal_file_name` varchar(255) DEFAULT NULL,
  `thumb_file_name` varchar(255) DEFAULT NULL,
  `full_file_name` varchar(255) DEFAULT NULL,
  `photo_order` int(11) DEFAULT NULL,
  `gallery_file_name` varchar(255) DEFAULT NULL,
  `photo_type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_photos_OWNER_ID` (`owner_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

The normal_file_name, thumb_file_name, gallery_file_name and full_file_name are fields with file paths.

Thanks for help in advance!

// Juriy

Upvotes: 7

Views: 8594

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

Use REPLACE. As an example:

update photos
    set normal_file_name = replace(normal_file_name, '.png', '.jpg');

Upvotes: 4

OMG Ponies
OMG Ponies

Reputation: 332681

Use the REPLACE function in an UPDATE statement:

UPDATE PHOTOS
   SET normal_file_name = REPLACE(normal_file_name, '.png', '.jpg'),
       thumb_file_name = REPLACE(thumb_file_name, '.png', '.jpg'),
       gallery_file_name = REPLACE(gallery_file_name, '.png', '.jpg'),
       full_file_name = REPLACE(full_file_name, '.png', '.jpg')

If there's no match, the replacement won't happen.

Upvotes: 17

Related Questions