T-SQL

Question

T-SQL

Hema Mutreja

Right there with Babe

Points: 772
More actions
May 5, 2008 at 11:03 pm

#71256

Hi,
I have a column which stores comma separated values eg.: 5,2,3,4,23,5,45,33,6,7,5,3
I want to check for any occurence of a value 5 (only digit 5, not 45) and remove it from the string, What will be the best way to do it?
Any help is greatly appreciated!!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]First convert the CSV, I mean Row to Column and insert the values into temp table then write a simple query on that temp table like:

Delete From tempTable Where value = 5

Mahesh[/font]

MH-09-AM-8694

Hema Mutreja Right there with Babe Points: 772 More actions · Answer 2

Thanks Mahesh!!!

But, how do I convert it into columns?

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 3

[font="Verdana"]Write a function and pass the whole string to it as a parameter. And inside the function write a loop and check the occurance of comma when you found comma insert the string into temp table. i.e.

...

Set @i = 1

Set @STR = '5,10,15,25,45,55,...'

Select @cnt = Len(@str)

While @i < @cnt

if right(@str, @i) = ',' then

begin

insert into #Table Values (@val)

set @val = ''

end

else

begin

@val = @val + right(@str, @i)

end

...

and then you can write Delete statement on this temp table to remove the values you wants to delete.

confirm on this.

Mahesh

[/font]

MH-09-AM-8694

Hema Mutreja Right there with Babe Points: 772 More actions · Answer 4

Thanks a lot!!!

I'll try this and let you know....

Thanks..

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 5

[font="Verdana"]I got some stuff on net.

Splitting Strings into table-rows, based on a specified delimiter

-----------------------------------------------------------------

Imagine you have a string which you want to break into words to make

into a table*/

DECLARE @ordinal VARCHAR(255)

SELECT @Ordinal=

'first second third fourth fifth sixth seventh eighth ninth tenth'

/*

This can be done very simply and quickly through the following single

SQL Select statement:

(make sure you have executed the spMaybeBuildNumberTable procedure

first!)*/

SELECT SUBSTRING(@Ordinal+' ', number,

CHARINDEX(' ', @Ordinal+' ', number) - number)

FROM Numbers

WHERE number <= LEN(@Ordinal)

AND SUBSTRING(' ' + @Ordinal,

number, 1) = ' '

ORDER BY number RETURN

/*----with the result

first

second

third

fourth

fifth

sixth

seventh

eighth

nineth

tenth

here your delimeter will be comma in place of space

Mahesh

[/font]

MH-09-AM-8694

Hema Mutreja Right there with Babe Points: 772 More actions · Answer 6

This is what we did it the end to achieve the solution:

declare @STR varchar(50),@z varchar(10),@y int

set @STR = ','+(select VAL from TEST1 where ID ='3') + ','

select @z = ',3,'

select @STR

SELECT REPLACE(@STR, @Z, ',')

These are the values that I have in my table

ID Val

11,2,3,4,5,23,33,44,56

233,2,4,,34,534,56

33,3,33,3333,3

running this code for the above ID as 3 gives the result:

,3,33,3333,

the only problem with this is that it looks only for the first match it gets, but I guess that can be taken care of by using a loop.

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 7

[font="Verdana"]

This is the url where I got the stuff I posted earlier. Go through the whole article and confirm

http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/#first%5B/url%5D

Mahesh[/font]

MH-09-AM-8694