Recursive Example of Common Table Expression - GetAcronym

Below is an example of SQL function that takes text with space as separator and returns acronym with first letter of each seperated word.

Select dbo.GetAcronym('Hello World  Whats Up', ' ') 
/* There are 2 spaces between World and Whats */

will return HW WU

The function definition is as follows. It uses Common Table Expression with Union All to run a recursive query.

CREATE function dbo.GetAcronym
(
  @str varchar(4000), @separator char(1) = ' '
)
returns varchar(4000)
as
begin

declare @retVal varchar(4000)
if @separator is null
  begin
  set @separator = ' '
  end

;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 Query via Common Table Expression is used along with Union All */
  where b > 0
)

select @retVal = convert(
  varchar(4000), 
  (select substring (@str, a, 1) from tokens for xml path (''), type ), 
  1
)
return @retVal

end