Help Forumlate a Query...

Hello,

Say i have a table with only a url column.

Sample Data
----------------------------------------
http://whatever1
http://whatever2
http://whatever3
http://x.com/l/a.html
http://x.com/l/b.html
http://whatever4
http://x.com/l/c.html
http://x.com/l/d.html
http://whatever5
----------------------------------------

Now i wish to change all particular rows in the url col from x.com to x.com/new/[samepagename].htm.
For eg. http://x.com/l/a.html should change to http://x.com/new/a.htm and so on.

Also, after running the query the table should look like this:
Sample Data
----------------------------------------
http://whatever1
http://whatever2
http://whatever3
http://x.com/new/a.htm
http://x.com/new/b.htm
http://whatever4
http://x.com/new/c.htm
http://x.com/new/d.htm
http://whatever5
----------------------------------------

I am using mysql MySQL 4.0.25. Can anyone help me with a query to do this?

Thanks
Neville

You'll be better off doing this in a script instead of trying to set it up directly in your database. Then you can use regular expressions to make the replacements much easier.

Hi,

Yes, i do realize that this is possible to do using a script however using a query is a lot more quicker than writing a script. Anyway, if its not possible then a script i guess is the only option.

Thanks
Neville

Using a query may lead to bigger errors.
I second the motion for a script.
Especially given that some of your data includes a domain name AND path already.

Assuming this can even be done with a query (which I'm not sure it can), it'll still be quicker to write a script than it will to set up and properly test a query, because in a script you can use regular expressions instead of the stacks of SUBSTRING()'s and LOCATE()'s you'd need in a query.

Yep, a script is the best and quickest option. I could even beat the execution time of the (very difficult) query!
I wouldn't even start to write a query....