Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Performing substitutions in MySQL

by webchalkboard (Scribe)
on Apr 12, 2005 at 13:20 UTC ( [id://446975]=perlquestion: print w/replies, xml ) Need Help??

webchalkboard has asked for the wisdom of the Perl Monks concerning the following question:

Hello,

Firstly sorry this isn't strictly a Perl related question (although this problem has arrisen thanks to a bug in my last Perl script :) but i'm sure that people on here will know how to do what i'm trying to do.

Basically I have a database with lots and lots of records in it, there is a field called imageurl, within which I have entries like blablah.jpg. I need to change every imageurl field which ends in .jpg to .gif.

Now I could write a little perl script which does this for me automatically, but i'm sure there must be a way of doing substitions in MySQL directly?

Does anyone here have any experience they could share with me?

Thanks, Tom

Learning without thought is labor lost; thought without learning is perilous. - Confucius
WebChalkboard.com | For the love of art...

Replies are listed 'Best First'.
Re: Performing substitutions in MySQL
by dragonchild (Archbishop) on Apr 12, 2005 at 13:47 UTC 
    UPDATE my_table
   SET imageurl = CONCAT(
         LEFT(imageurl, LENGTH(imageurl) - INDEX( imageurl, '.jpg') )
        ,'.gif'\
       )

    [download]

    You'll have to play with it to make sure there isn't an off-by-one error.

    My wife's blog

[reply]
[d/l]
Re: Performing substitutions in MySQL
by kgraff (Monk) on Apr 12, 2005 at 14:00 UTC

    MySQL has a bunch of string manipulation functions for example:

    REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string
from_str replaced by the string to_str.

    To be able to play around with syntax, make a new column to hold the altered values, then when you get the results you want, delete the original column and rename the new one.

[reply]

Back to Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: perlquestion [id://446975]
Approved by moot
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others avoiding work at the Monastery: (7)
As of 2024-04-23 20:14 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found