13
Jan
2023
2023
Converting commas or other delimiters to a Table or List in SQL Server
by Shubham Batra
52
Create a function SplitString which is using a loop inside the table-valued function.
CREATE FUNCTION SplitString ( @in_string VARCHAR(MAX), @delimeter VARCHAR(1) ) RETURNS @list TABLE(item VARCHAR(100)) AS BEGIN WHILE LEN(@in_string) > 0 BEGIN INSERT INTO @list(item) SELECT left(@in_string, charindex(@delimeter, @in_string+',') -1) as Item SET @in_string = stuff(@in_string, 1, charindex(@delimeter, @in_string + @delimeter), '') end RETURN END
this function allows two arguments or input parameters (1 – input string and 2 – delimiter):
SELECT * FROM SplitString('india,america,asia,europe', ',')
This is how we can Converting commas or other delimiters to a Table or List in SQL Server