MySQL query for renaming part of the URL in a table

Question

I have just imported a database table from a previous site, and one of the table headers has a title of 'enclosure' and in this, there is a big list of URLs, all starting http://richardgordoncook.com/dl/audio/filename.mp3 etc.

I need to change the start of this URL from richardgordoncook.com to richgc.com but I don't know how to be that specific with a query.

I can easily change one:

UPDATE `db141188_cnc`.`wp_StreamPad_Tracks` SET `enclosure` = 'http://richgc.com/dl/audio/andy_summers.mp3',
`sourceUrl` = 'http://www.curatorialnoiseclub.com' WHERE `wp_StreamPad_Tracks`.`id` =5 LIMIT 1 ;

But I don't want to write each one out.

So, I need to change richardgordoncook.com to richgc.com.

Is there any way to do this?

Scott Hunter · Accepted Answer · 2012-01-18 18:16:36Z

3

Something like this:

update <table> 
SET enclosure = replace(enclosure, 'richardgordoncook.com', 'richgc.com')

answered Jan 18, 2012 at 18:16

Scott Hunter

50k12 gold badges64 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ypercubeᵀᴹ · Accepted Answer · 2012-01-18 18:29:22Z

0

UPDATE TableX   
  CROSS JOIN
    ( SELECT 'http://richardgordoncook.com'  AS original
           , 'http://richgc.com'             AS replacement
    ) AS c
SET enclosure 
    = CONCAT( replacement
            , RIGHT(enclosure, LEN(enclosure) - LEN(original))
            )
WHERE enclosure LIKE CONCAT(original, '%')

answered Jan 18, 2012 at 18:29

ypercubeᵀᴹ

116k19 gold badges181 silver badges248 bronze badges

Collectives™ on Stack Overflow

MySQL query for renaming part of the URL in a table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related