Hi,

I have a column which stores comma separated values eg.: 5,2,3,4,23,534,45,33,6,7,5,3

I want to check for any occurence of a value 5 (only digit 5, not 45 or 534) and remove it from the string, What will be the best way to do it?

Any help is greatly appreciated!!!

Thanks

DECLARE @col VARCHAR(80)
SELECT   @col = '5,2,3,4,23,534,45,33,6,7,5,3'

SELECT SUBSTRING(
         REVERSE
           (SUBSTRING
              (REVERSE
                 (REPLACE(',' + @col + ',', ',5,', ',')
              )
              , 2, 80)
           )
        , 2, 80)
--2,3,4,23,534,45,33,6,7,3

It looks clumsy but will work over a large table without iteration or cursors. the 80 is the length of the column in our example, but I've used a 80-char variable to test it with

Assuming using varchar(max) I used the STUFF function in the following sql script



DECLARE @str VARCHAR(max), @val VARCHAR(max)
SELECT @str = '5,2,3,4,23,534,45,33,6,7,5,3', @val = '5'

SELECT REVERSE(STUFF(REVERSE(STUFF(REPLACE(',' + @str + ',', ',' + @val + ',' , ','), 1, 1, '')), 1, 1, ''))

---

Eralper
http://www.kodyaz.com
http://www.eralper.com