Posted by George on 10/14/22 11:59

On Sun, 17 Sep 2006 13:58:57 +0200, Chris wrote...
>
>Bob Stearns wrote:
>
>> Chris wrote:
>>
>>> Hello,
>>>
>>> I have a table that contains the following records.
>>>
>>>
>+-----+-----+-----------+---------------------------------+------------+----------+
>>> | fid | nid | filename | filepath | filemime |
>>> filesize |
>>>
>+-----+-----+-----------+---------------------------------+------------+----------+
>>> | 1 | 1 | _original | images/IMG_3002.JPG | image/jpeg |
>>> 1930295 |
>>> | 2 | 1 | thumbnail | images/IMG_3002.thumbnail.JPG | image/jpeg |
>>> 1854 |
>>> | 3 | 1 | preview | images/IMG_3002.preview.JPG | image/jpeg |
>>> 21818 |
>>> | 4 | 2 | _original | images/IMG_3002_0.JPG | image/jpeg |
>>> 1930295 |
>>> | 5 | 2 | thumbnail | images/IMG_3002_0.thumbnail.JPG | image/jpeg |
>>> 1854 |
>>> | 6 | 2 | preview | images/IMG_3002_0.preview.JPG | image/jpeg |
>>> 21818 |
>>>
>+-----+-----+-----------+---------------------------------+------------+----------+
>>>
>>> What I need to do, is change all the rows where the filepath column has a
>>> value that contains the characters _0.
>>>
>>> I've selected these rows by doing:
>>>
>>> select * from files where filepath like '%_0.%';
>>>
>>> although I'm sure that there will be a better query to do the same.
>>> However I have no idea how I could change the values for that column.
>>>
>>> SO what I need at the end is:
>>>
>>>
>+-----+-----+-----------+---------------------------------+------------+----------+
>>> | fid | nid | filename | filepath | filemime |
>>> filesize |
>>>
>+-----+-----+-----------+---------------------------------+------------+----------+
>>> | 1 | 1 | _original | images/IMG_3002.JPG | image/jpeg |
>>> 1930295 |
>>> | 2 | 1 | thumbnail | images/IMG_3002.thumbnail.JPG | image/jpeg |
>>> 1854 |
>>> | 3 | 1 | preview | images/IMG_3002.preview.JPG | image/jpeg |
>>> 21818 |
>>> | 4 | 2 | _original | images/IMG_3002.JPG | image/jpeg |
>>> 1930295 | ^^^
>>> | 5 | 2 | thumbnail | images/IMG_3002.thumbnail.JPG | image/jpeg |
>>> 1854 | ^^^
>>> | 6 | 2 | preview | images/IMG_3002.preview.JPG | image/jpeg |
>>> 21818 | ^^^
>>>
>+-----+-----+-----------+---------------------------------+------------+----------+
>>>
>>>
>>> Thanks,
>>>
>>> Chris
>> I don't use MySQL, but in DB2 it would be as shown below. Untested.
>>
>> UPDATE files
>> SET filepath=SUBSTR(filepath,1,POSSTR(filepath,'_O.')-1) ||
>> SUBSTR(filepath,POSSTR(filepath,'_O.')+3)
>> WHERE POSSTR(filepath,'_O.')>0
>Thank you for pointing me in the right direction. It turns out that it can
>be done in Mysql like this:
>
>mysql> update files set filepath=replace(filepath,'_0','');
>
>Thanks again,
>
>Chris


Thanks for the information. I haven't use replace before but could find some
uses for that in the future.

George
--
Help a Community by Participating in Ours
We donate your subscription fees to the charity you choose
100% of your first month, 10% thereafter.
http://newsguy.com/charity.asp

[Back to original message]