you could do substring....
update table_name set col_name=concat(substring_index(col_name,".pdf",1),".zip") where col_name like "%.pdf"
What does this do? From the man page:
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count occurrences of the
delimiter delim. If count is positive, everything to the left of the
final delimiter (counting from the left) is returned. If count is
negative, everything to the right of the final delimiter (counting from
the right) is returned:
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
This function is multi-byte safe.
What does it do here?
mysql> select concat(substring_index("filename.pdf",".pdf",1),".zip");
+---------------------------------------------------------+
| concat(substring_index("filename.pdf",".pdf",1),".zip") |
+---------------------------------------------------------+
| filename.zip |
+---------------------------------------------------------+
The ONLY problem you will have with this is if the filename is something like this:
filename.pdffile.pdf
This code will rename it filename.zip, not filename.pdffile.zip as expected.
Peter
On Fri, 15 Nov 2002, Brad Bonkoski wrote:
> If you wish to update all of them, then just eliminate the id condition.
>
> chip.wiegand@simrad.com wrote:
>
> > I have a database with several hundred entries of file names that end with
> > .pdf. I have converted
> > all those docs to .zip, now I need to change all the entries in the
> > database to .zip. I tried to use
> > update table_name set col_name='%.zip' where col_name like '%.pdf' && id
> > = '11'
> > but of course that changed the file name for id 11 to %.zip. Is there a way
> > to change all the
> > entries from .pdf to .zip without writing an update statement for each
> > individual row?
> >
> > --
> > Chip Wiegand
> > Computer Services
> > Simrad, Inc
> > www.simradusa.com
> > chip.wiegand@simrad.com
> >
> > "There is no reason anyone would want a computer in their home."
> > --Ken Olson, president, chairman and founder of Digital Equipment
> > Corporation, 1977
> > (They why do I have 8? Somebody help me!)
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
---------------------------------------------------------------------------
Peter Beckman Systems Engineer, Fairfax Cable Access Corporation
beckman@purplecow.com http://www.purplecow.com/
---------------------------------------------------------------------------
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
