The following will create a user defined function (in the master table), and will allow a user to essentially split an entire string into individual rows by a character.

Why use it? I found it to be useful for creating a translation/obfuscation results.

/*   ____  _____ _____ 
    |    \|     |     |
    |  |  |   --| | | |
    |____/|_____|_|_|_|
      Author: RJ Hatch
 Create date: 2014-04-07
 Description: Splits a string into a table
  Parameters:
    @Input - String to split into a table
     Returns: Table
                Id INT
                Value NVARCHAR(1)
*/

IF EXISTS (SELECT * FROM sys.objects WHERE name='udf_TextToTable' AND schema_id=SCHEMA_ID('dbo') AND type='TF')
BEGIN
    DROP FUNCTION dbo.udf_TextToTable
END
GO

CREATE FUNCTION dbo.udf_TextToTable
(
    @Input NVARCHAR(MAX)
)
RETURNS 
@Return TABLE (Id INT, Value NVARCHAR(1))
AS
BEGIN
	-- Fill the table variable with the rows for your result set

    INSERT INTO @Return
        SELECT  T.ID, SUBSTRING(@Input, T.ID, 1) Value
        FROM    master.dbo.Tally T
        WHERE   T.ID BETWEEN 1 AND LEN(@Input)

    RETURN
END
GO

SELECT * FROM dbo.udf_TextToTable('Welcome to Denver Code Monkey!')

RJ Hatch

RJ writes custom Windows and Web applications using the Microsoft .NET framework, and enjoys solving problems with code, often while listening to a techno soundcloud stream. He hopes to one day write an application that many businesses will use.