Split Delimited Value Into Tabular Form

  1. DECLARE @Temp TABLE (Data VARCHAR(512))  
  2. DECLARE @ComChar    VARCHAR(512)='Delhi,Mumbai,Chennai,Calcutta',  
  3. @Seperator  VARCHAR(5)=','  
  4. WHILE CHARINDEX(@Seperator,@ComChar,1)<>0  
  5. BEGIN  
  6. INSERT INTO @Temp(Data)  
  7. SELECT SUBSTRING(@ComChar,1,CHARINDEX(@Seperator,@ComChar,1)-1)  
  8. SET @ComChar=SUBSTRING(@ComChar,CHARINDEX(@Seperator,@ComChar,1)+1,LEN(@ComChar))  
  9. END  
  10. INSERT INTO @Temp(Data)  
  11. SELECT @ComChar  
  12. SELECT * FROM @Temp