T-SQL SplitString function - konstantinos.kourentzes.com

CREATE FUNCTION dbo.SplitString
(
   @String NVARCHAR(MAX),
   @Delimiter NVARCHAR(10)
)
RETURNS @Result TABLE (Value NVARCHAR(MAX))
AS
BEGIN
   DECLARE @Value NVARCHAR(MAX)
   DECLARE @Pos INT

   WHILE CHARINDEX(@Delimiter, @String) > 0
   BEGIN
      SET @Pos = CHARINDEX(@Delimiter, @String)
      SET @Value = SUBSTRING(@String, 1, @Pos - 1)
      INSERT INTO @Result (Value) VALUES (@Value)
      SET @String = SUBSTRING(@String, @Pos + LEN(@Delimiter), LEN(@String))
   END

   INSERT INTO @Result (Value) VALUES (@String)
   
   RETURN
END

With this function in place, you can use it to split a string into rows like this:

SELECT Value
FROM dbo.SplitString('Apple,Orange,Banana', ',');

This will output:

value
------
Apple
Orange
Banana

Please make sure to check the compatibility of your SQL Server version, as the STRING_SPLIT() function is available in SQL Server 2016 and later versions.

SELECT value
FROM STRING_SPLIT('Apple,Orange,Banana', ',');

Related Posts