Installing a STRING_SPLIT function in SQL 2008 and SQL 2012

If you're like me, you often run into a situation where Microsoft has been kind enough to solve an issue or add functionality to an obvious gap in a previous version (like the function ISNUMERIC() ), but you are unable to upgrade to the newer SQL version for one of a never-ending list of reasons ("the application only supports SQL 2000", "there's no money in the budget for that", "why fix what isn't broken", "the moon is full", "I ate bad sushi yesterday and i'm just not 'feelin it' today").

At any rate, I needed to use the STRING_SPLIT function available in 2016 forward on a 2008 R2 box, so I wrote code that emulated that table-valued function and can be used interchangeably.

I also added a piece that allows for escaped delimiters using \delimiter\, so you may find utility for this even in a SQL 2016 instance.

Running this script installs the function in MASTER and then creates a synonym in each database as well as MODEL, so any new databases will have it as well.

Enjoy!

USE master

go

IF Object_id('string_split') IS NULL
  EXEC ( 'CREATE FUNCTION String_split () returns @result TABLE (   value VARCHAR(max))   BEGIN       DECLARE @TEMP AS VARCHAR(4)       RETURN   END' )

go

ALTER FUNCTION String_split (@string    VARCHAR(max),
                             @separator VARCHAR(10))
returns @result TABLE (
  value VARCHAR(max))
AS
  BEGIN
      DECLARE @pos    INT = 0,
              @res    VARCHAR(100),
              @escape VARCHAR(30),
              @escomb VARCHAR(max)

      SET @escape = '\' + @separator + '\'
      SET @escomb = Replace(@string, @escape, '<escapeddelimiterreplacement>')

      WHILE Len(@escomb) > 0
        BEGIN
            SET @pos = Charindex(@separator, @escomb + @separator, 0)

            SELECT @res = LEFT(@escomb, @pos),
                   @escomb = Substring(@escomb, @pos + 1, 100)

            INSERT @result
            VALUES (Replace(@res, @separator, ''))
        END

      UPDATE @result
      SET    value = Replace(value, '<escapeddelimiterreplacement>', @escape)

      RETURN
  END

go

-- make this function available in all databases
DECLARE @dsql AS NVARCHAR(4000) = 'DECLARE @cnt as int '

SELECT @dsql = @dsql + 'USE ' + NAME + ' select @cnt = count(*) from sys.objects where name = ''string_split'' IF @cnt = 0 BEGIN CREATE SYNONYM dbo.string_split FOR master.dbo.string_split END '
FROM   sys.databases
WHERE  NAME NOT IN ( 'master', 'tempdb', 'msdb', 'Resource' ) -- model is omitted to make sure it appears in any new databases created

EXEC (@dsql)

Facebook Social Comments

The Jargon Bin

Tags

Home

Installing a STRING_SPLIT function in SQL 2008 and SQL 2012