Remove '.png' from string in sql server

Question

Basically I have a column in SQL Server that has icon image names

It's kind of like

ICON
------------
Icon001
Icon002.png
Icon003.png
Icon004.png
Icon005
Icon006.png
Icon007.png

I'm trying to figure out how I can write a script to remove all of the .png from the ones that have it

I have tried

Update [dbo].[screen].[icon] 
set ICON = ICON - '%.png%' 
where ICON LIKE '%.png%'

But that doesn't work.

Can anybody help me?

Hint: [`REPLACE()`](http://msdn.microsoft.com/en-us/library/ms186862.aspx) and of course research on [previous related questions](http://stackoverflow.com/search?q=sql+server+remove+part+of+string) — Pondlife, Mar 22 '13 at 21:17

score 2 · Accepted Answer · answered Mar 22 '13 at 21:18

2

Try

... set ICON=LEFT(icon, LEN(icon)-4 ) where ICON like '%.png'

answered Mar 22 '13 at 21:18

Marc B

why not? what if one of the records is `image.png.png`? replace'll kill both .png's – Marc B Mar 22 '13 at 21:21
I stand corrected. It's an unlikely occasion, but it could happen. – Yuriy Galanter Mar 22 '13 at 21:24
1

yep, unlike, but I've learned to not trust in "unlikely". as the old statement goes: million-to-one-chances have a 50:50 chance of occuring: either they do, or they don't. – Marc B Mar 22 '13 at 21:25

score 2 · Answer 2 · answered Mar 22 '13 at 21:21

2

...SET ICON = REPLACE(ICON, '.png','')

Should do the trick

answered Mar 22 '13 at 21:21

Yuriy Galanter

score 0 · Answer 3 · answered Mar 22 '13 at 21:19

0

Maybe something like

UPDATE icon SET icon = LEFT(icon, CHARINDEX('.png', icon) - 1) WHERE icon LIKE '%.png%'

answered Mar 22 '13 at 21:19

Kermit

what about a malformed filename like `image.png.gif.png`? – Marc B Mar 22 '13 at 21:22
@MarcB Out of scope of the question. – Kermit Mar 22 '13 at 21:22

3 Answers3