[PHP-DB] database update question
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 [EMAIL PROTECTED] 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
Re: [PHP-DB] database update question
Is there a way to change all the entries from .pdf to .zip without writing an update statement for each individual row? UPDATE table_name SET col_name = LEFT(col_name, LEN(col_name) - 4) + '.zip' WHERE col_name LIKE '%.pdf' -j. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] database update question
On Saturday 16 November 2002 00:53, [EMAIL PROTECTED] 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? UPDATE table_name SET col_name = REPLACE(col_name, '.pdf', '.zip'); -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Politicians should read science fiction, not westerns and detective stories. -- Arthur C. Clarke */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] database update question
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 [EMAIL PROTECTED] 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
Re: [PHP-DB] database update question
If you wish to update all of them, then just eliminate the id condition. [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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
Re: [PHP-DB] database update question
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.
[EMAIL PROTECTED] 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
[EMAIL PROTECTED]
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 BeckmanSystems Engineer, Fairfax Cable Access Corporation
[EMAIL PROTECTED] http://www.purplecow.com/
---
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
