CODEWITHSUNDEEP
mysqldatabase
Mr Yar
Mr Yar

Reputation: 349

Add extra characters to specific rows in mysql

How can i add some characters before and after all *.jpg addresses in this table:

id  value     type 
1   a.jpg     photo     
2   b         text
3   c.jpg     photo

here is what i want as result:

id  value                                  type 
1   [img]http://www.b.com/a.jpg[/img]     photo     
2   b                                      text
3   [img]http://www.b.com/c.jpg[/img]     photo

Upvotes: 1

Views: 41

Answers (2)

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

UPDATE SAMPLETABLE SET COLUMNNAME=CONCAT('[img]http://www.b.com/',COLUMNNAME,'[/img]') WHERE COLUMNNAME LIKE '%.jpg%'

Try above code.Hope this will helps.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I would tend to do:

update t
    set value = replace('[img]http://www.b.com/{0}[/img]', '{0}', value)
    where value like '%.jpg';

I like to use replace() for constructing strings, because it is easier to see and modify the format of the string.

Upvotes: 1

Related Questions