Recursive example of Common Table Expression - SplitString

Below is an example of Recursive query using SQL Server's Common Table Expression.

declare @s varchar(2000)
set @s = 'Hello World SQL DBAs'

select *
from dbo.SplitString(@s, ' ');
go

will return

index	value
0	Hello
1	World
2	SQL
3	DBAs

Function definition using recursive query via Common Table Expression and Union All:

CREATE function [dbo].[SplitString] 
(
  @str nvarchar(4000), 
  @separator char(1)
)
returns table
AS
return (
        
  with tokens(p, a, b) AS 
  (
    select  1, 1, charindex(@separator, @str)
           
    union all
            
    select p + 1, b + 1, charindex(@separator, @str, b + 1)
    from tokens /* Recursive use of common table expression */
    where b > 0
    )
        
    select 
      p-1 as [index]
      ,substring(@str, a, case when b > 0 then b-a ELSE 4000 end) AS [value]
    from tokens
)