declare @str varchar(200)
declare @chr char(1)
declare @val numeric
declare @cnt numeric
set @cnt = 0
set @str = ‘1,22,11,0,5’
if right(@str,1)<>‘,’
begin
set @str = @str+‘,’
end
set @val = (len(@str) – len(replace(@str,‘,’,”)))
while @cnt < @val
begin
print substring(@str,1,patindex(‘%,%’,@str)- 1)
set @str = substring(@str,patindex(‘%,%’,@str) + 1,len(@str) – patindex(‘%,%’,@str))
set @cnt = @cnt + 1
end
In some cases we need to manipulate data where we need to process one set of data in the form of one string.
For example, one student need to check his subjects from the subject list, where subject are kept as check box in an web page. In this case suppose we have table called StudentSubject where columns are like StudentId, SubjectId then pass in a stored procedure StudentId and concatanaded all the SubjectIds in a string and pass and in the SP these SubjectIds will be splitted to insert into the table. But before inserting don’t forget to delete the records from the table for that Student’s subjects.