I am using stuff function to insert a value which is separated by comma.
Now I want to retrieve those value and each value which is separated by comma is unique and store these value in another variable and use them e.g product_id(10,5,9).
This is one row data now I want to read and store there id in separate variable
Check this example. may be work for you. But this is for a single record, if you wish, then you need again a extra loop above this loop.
declare @S varchar(30)
set @S = '10,5,9'
declare @tempVariableTostoreID int
while len(@S) > 0
begin
--here you can store it on another variable
select @tempVariableTostoreID = LOWER( left(@S, charindex(',', @S+',')-1) )
select @tempVariableTostoreID
set @S = stuff(@S, 1, charindex(',', @S+','), '')
end
May I ask a question, why you split and need to store in again variable. What is your goal?
Updated
As per you comment check this. First you create a table-valued function from any of this link and then use this sqlscript.
Create Split function:
http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
Splitting delimited values in a SQL column into multiple rows
How to split a single column values to multiple column values?
create table product (productid int, productname ntext)
create table orderhistory (orderID int, username varchar(100), productid varchar(100) )
insert into product values(1, 'P1')
insert into product values(2, 'P2')
insert into product values(3, 'P3')
insert into product values(4, 'P4')
insert into product values(5, 'P5')
select orderid, username , p.productname from
(
select oh.orderid, oh.username ,item from orderhistory oh
cross apply dbo.Split(oh.productid , ',')
)
a join product p on p.productid = a.item
