SQL Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Markus Bott - 29 Aug 2003 09:55 GMT

Hi,

i am looking for a solution for the following problem.

i got filenames saved a strings in a database. filenames look like :
'test1.jpg', 'test2.jpg' and so on ....

Some files have been imported wrong, they are named like 'test4 copy.jpg'.

My question is ... is it possible to rename the filenames with a sql
query, so that the 'copy' part will be deleted ?

any help on this problem would be great

Greetings,

Markus

Reply to this Message

Jarl - 29 Aug 2003 11:17 GMT

> Hi,
>
[quoted text clipped - 8 lines]
> My question is ... is it possible to rename the filenames with a sql
> query, so that the 'copy' part will be deleted ?

Try this:

UPDATE files_table
SET file = SUBSTRING(file FROM 1 FOR POSITION(' copy' IN file)-1) ||
SUBSTRING(file FROM POSITION(' copy' IN file) + 5 FOR
CHARACTER_LENGTH(file) - POSITION(' copy' IN file)-1 + 5)
WHERE
LOWER(file) LIKE '% copy.%';

/Jarl

Reply to this Message

Markus Bott - 29 Aug 2003 11:57 GMT

>>Hi,
>>
[quoted text clipped - 19 lines]
>
> /Jarl

i got it solved :

UPDATE table SET name=REPLACE(name, ' copy','')

Thx.

Markus

Reply to this Message