CODEWITHSUNDEEP
mysql
Hkachhia
Hkachhia

Reputation: 4539

Change image name and update into same table using mysql query

I want to change image name in table like below.

image name : test.png

replace with : test_E.png

I want _E at end of all image name in table using mysql query.

Upvotes: 0

Views: 215

Answers (2)

Rutesh Makhijani
Rutesh Makhijani

Reputation: 17235

You can use string functions of MySQL query:

UPDATE TABLE SET IMAGE_NAME = CONCAT(SUBSTR(IMAGE_NAME,(CHAR_LENGTH(IMAGE_NAME) - 4)),
'_E' , SUBSTR(IMAGE_NAME, -4)) WHERE ID = <put record id>;

SUBSTR(IMAGE_NAME,(CHAR_LENGTH(IMAGE_NAME)-4)) would return name of file - assuming extension is of 3 chars. For 'test.png' above function would remove '.png' and function would return 'test'

SUBSTR(IMAGE_NAME, -4) would return last four chars of string - so 'test.png' would return '.png'

using concat you can concat 'test', '_E' and '.png' - returning 'test_E.png'

Please refer to string functions reference of MySQL for further use

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24086

Use replace function

update <table>
set image=replace(image,'.png','_E.png')

you could use this, if the image extension is not same in the table

   update <table>
     set image=concat(substring(image,1,locate('.',image)-1),'_E',
                 substring(image,locate('.',image),lenght(image)))

Upvotes: 3

Related Questions