MS-SQL Convert String To Table List

Create a function

When we start programming in any language, mostly we use loop and we hit Server side code to list the data every time. This function is helpful in reducing our Server side code & increase Application performance. 

  1. CREATE FUNCTION[dbo].[UF_StrToTable](@String VARCHAR(MAX), @Delimiter CHAR(1))  
  2. RETURNS @Temptable TABLE(Result VARCHAR(8000))  
  3. AS  
  4. BEGIN  
  5. DECLARE @INDEX int, @SLICE VARCHAR(8000)  
  6. SELECT @INDEX = 1  
  7. IF LEN(@String) < 1 OR @String IS NULL  
  8. return  
  9. WHILE @INDEX != 0  
  10. BEGIN  
  11. SET @INDEX = CHARINDEX(@Delimiter, @String)  
  12. IF @INDEX != 0  
  13. BEGIN  
  14. SET @SLICE = LEFT(@String, @INDEX - 1)  
  15. END  
  16. ELSE  
  17. BEGIN  
  18. SET @SLICE = @String  
  19. END  
  20. IF(LEN(@SLICE) > 0)  
  21. BEGIN  
  22. INSERT INTO @Temptable(Result) VALUES(@SLICE)  
  23. END  
  24. SET @String = RIGHT(@String, LEN(@String) - @INDEX)  
  25. IF LEN(@String) = 0  
  26. break  
  27. END  
  28. RETURN  
  29. END   

Output

We can send the list as a string, as it converts against as a list of rows.

  1. SELECT Result FROM DBO.UF_StrToTable('1,2,3,4,5',',')  

Run the query given above in SQL Server & see the result.