Error inserting value into Table

I have a string which has values separated by comma. I need to insert the individual value separated by comma into a table. I have written following code but values are inserted into the table. Could any one help me find the mistake in my code.

   declare @str varchar(25)
   set @str = 'a,b,c'
   Create table #Qw(parts varchar(25))
   while(patindex(',',@str)>0)
   begin       
         insert into #开发者_StackOverflow社区Qw values(substring(@str,1,1))
   end
   select * from #Qw

Some issues:

• You need to use charindex instead of patindex.
• You need to change the string inside the loop, or it will never exit.
• You need to use the index of the comma to get the substring, otherwise it will only work for single character items.

:

declare @str varchar(25)
set @str = 'a,b,c'

create table #Qw (parts varchar(25))

while (charindex(',', @str) > 0) begin
  -- get first part of the string up to the first comma
  insert into #Qw values(substring(@str, 1, charindex(',', @str) - 1))
  -- remove first part of the string including the comma
  set @str = substring(@str, charindex(',', @str) + 1, 100)
end

-- insert the last item from the string
insert into #Qw values(@str)

select * from #Qw

Result:

+-------+
| parts |
+-------+
| a     |
| b     |
| c     |
+-------+

This should work and will still work even if the strings are not all single characters.

DECLARE @str varchar(25)
DECLARE @Pos int  

SET @str = 'a,b,c'

Create table #Qw(parts varchar(25))   

WHILE(CHARINDEX(',',@str)>0)   
BEGIN
    PRINT LEFT(@str,CHARINDEX(',',@str)-1)

    INSERT INTO #Qw(parts) VALUES(LEFT(@str,CHARINDEX(',',@str)-1))   
    SET @str = RIGHT(@str,LEN(@str) - CHARINDEX(',',@str))
END

INSERT INTO #Qw(parts) VALUES(@str)   

SELECT * FROM #Qw