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!
USEmaster
go
IFObject_id('string_split')ISNULL EXEC('CREATE FUNCTION String_split () returns @result TABLE ( value VARCHAR(max)) BEGIN DECLARE @TEMP AS VARCHAR(4) RETURN END')
go
ALTERFUNCTIONString_split(@stringVARCHAR(max), @separatorVARCHAR(10)) returns@resultTABLE( valueVARCHAR(max)) AS BEGIN DECLARE@posINT=0, @resVARCHAR(100), @escapeVARCHAR(30), @escombVARCHAR(max)
-- make this function available in all databases DECLARE@dsqlASNVARCHAR(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 ' FROMsys.databases WHERENAMENOTIN('master','tempdb','msdb','Resource')-- model is omitted to make sure it appears in any new databases created