update query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

update query help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-12-12 : 03:35:16

I have the following query

update items set picture1url='images/products3/tm' + picture1url where itemid>334

Now I want to change it to do the above but to remvoe the '.jpg' at the end of picture1url and add a '_jpg.jpg'

Can someone help me with the code?

Kristen
Test

22859 Posts

Posted - 2004-12-12 : 03:52:00

[code]update items
set picture1url='images/products3/tm' + REPLACE(picture1url, '.jpg', '_jpg.jpg')
where itemid>334
[/code]
Note that this will replace ALL occurrences of .JPG - even within the filename. However, I don't suppose there are any.

The other alternative is to use string manipulation to remove the RIGHT most 4 characters (".JPG"), and append '_jpg.jpg', but I think this is more risky because I expect it IS quite likely that you will have file extensions of .GIF or something else (if not now, then in the future)

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-12 : 09:52:33

do it in two updates
update items
set picture1url='images/products3/tm' + picture1url
where itemid>334

update items
set picture1url = left(picture1url, len(picture1url) - 4) + '_jpg.jpg'
where itemid>334
and right(picture1url,4) = '.jpj'

or in one

update items
set picture1url='images/products3/tm' + case when right(picture1url,4) = '.jpj' then left(picture1url, len(picture1url) - 4) + '_jpg.jpg' else picture1url end
where itemid>334

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources