Martina Ponca's Blog

DECLARE @statement NVARCHAR(MAX);
SET @statement = N'
CREATE FUNCTION [dbo].[SplitWordToLetters]
(
      @StringToSplit nvarchar(2000)
)
RETURNS @RtnValue table
(
      Id int identity(1,1),
      Letter NCHAR(1)
)
AS
BEGIN
      Declare @substring nvarchar(2000)
      Declare @letter NCHAR(1)

      Set @substring = @StringToSplit 

      While (LEN(@substring) > 0)
      Begin
            SELECT @letter = LEFT(@substring, 1), @substring = SUBSTRING(@substring, 2, 255)

            Insert into @RtnValue (Letter) values (@letter)
      End

      Return
END '

IF OBJECT_ID(N'[dbo].[SplitWordToLetters]', N'TF') IS NULL
BEGIN
      EXECUTE (@statement);
END
ELSE
BEGIN
      SET @statement = REPLACE(@Statement, 'CREATE FUNCTION', 'ALTER FUNCTION');
      EXECUTE (@statement);
END

Usage:

SELECT *
FROM dbo.SplitWordToLetters ('abc')

/* Result:
Id          Letter
----------- ------
1           a
2           b
3           c

(3 row(s) affected)
*/