Split a given string into words based on the delimiter.

We can split a string into different words based on the delimiter. This example shows how we can achieve this. We can also use recursive CTE but if the string is too long MAXRECURSION can be encountered.

--DECLARE THE VARIABLES

DECLARE @String NVARCHAR(4000)
DECLARE @Delimiter NVARCHAR(10)
DECLARE @DelimiterPosition INT
DECLARE @StartofString INT
DECLARE @EndofString INT
DECLARE @DelimiterLength INT

--INITIALIZE THE VARIABLES

SET @String = 'Welcome,to,the.'
SET @Delimiter = ','
SET @DelimiterLength = LEN('.'+@Delimiter+'.')-2 --WILL BE USEFUL WHEN WE HAVE WHITE SPACE AS DELIMITER
SET @StartofString = 1
SET @EndofString = LEN(@String)+1
SET @DelimiterPosition = 0

WHILE (@DelimiterPosition < @EndofString)
BEGIN

SET @DelimiterPosition = CHARINDEX(@Delimiter,@String,@StartofString)
IF(@DelimiterPosition = 0)
SET @DelimiterPosition = @EndofString --FOR THE LAST SPLIT WORD, THE DELIMITER POSITION WILL BE ONE. SO, WE SET IT TO END OF THE STRING
PRINT SUBSTRING(@String,@StartofString,@DelimiterPosition-@StartofString)
SET @StartofString = @DelimiterPosition+@DelimiterLength
END


If we want to use CTE the following code will do exactly as above


DECLARE @Delimiter NVARCHAR(10)
DECLARE @String NVARCHAR(4000)
SET @String = 'Welcome,to,the,new'
SET @Delimiter = ','

;WITH Split
AS(
SELECT SUBSTRING(@String+@Delimiter,1,CHARINDEX(@Delimiter,@String+@Delimiter)-1) AS Word
,SUBSTRING (@String+@Delimiter,CHARINDEX(@Delimiter,@String+@Delimiter)+1,LEN(@String+@Delimiter)) As Remaining
   
    UNION ALL
   
SELECT SUBSTRING(Remaining,1,CHARINDEX (@Delimiter,Remaining)-1) AS Word
,SUBSTRING (Remaining,CHARINDEX(@Delimiter,Remaining)+1,LEN(Remaining)) As Remaining
     
FROM split
  WHERE CHARINDEX (',',Remaining)<>0
  )
 
  SELECT Word FROM Split