SQL Server String to Table User Defined Function

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!')
Profile photo of superadmin

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.

Twitter 

Comments are closed.