SQL Server : Split Comma Delimited Text (String)

The following is an example of text with comma separators:

'Indonesia,Singapore,Vietnam'

SQL Server 2016 or later includes the STRING_SPLIT function, which can be used to split text based on a specific separator character. For the above string example, a text string with a comma separator like the following query:

SELECT * 
FROM STRING_SPLIT('Indonesia,Singapore,Vietnam',',')

Another sample is a text with hash tag separators:

'Indonesia#Singapore#Vietnam'

The query will be like this:

However we can't use STRING_SPLIT for SQL Server versions before 2016. But we still can do it by making our own function using loop as below:

CREATE FUNCTION [dbo].StringSplit
(
    @String  VARCHAR(MAX), @Separator CHAR(1)
)
RETURNS @RESULT TABLE(Value VARCHAR(MAX))
AS
BEGIN     
 DECLARE @SeparatorPosition INT = CHARINDEX(@Separator, @String ),
        @Value VARCHAR(MAX), @StartPosition INT = 1
 
 IF @SeparatorPosition = 0  
  BEGIN
   INSERT INTO @RESULT VALUES(@String)
   RETURN
  END
     
 SET @String = @String + @Separator
 WHILE @SeparatorPosition > 0
  BEGIN
   SET @Value = SUBSTRING(@String , @StartPosition, @SeparatorPosition- @StartPosition)
 
   IF( @Value <> ''  ) 
    INSERT INTO @RESULT VALUES(@Value)
   
   SET @StartPosition = @SeparatorPosition + 1
   SET @SeparatorPosition = CHARINDEX(@Separator, @String , @StartPosition)
  END    
     
 RETURN
END

Execute the SQL above then a function named StringSplit will be created. And here is how to use:

SELECT * FROM
 StringSplit('Indonesia,Singapore,Vietnam',',')
SELECT * FROM
 StringSplit('Indonesia#Singapore#Vietnam','#')