[SQL] sql version of Split function. For splitting strings into single column tables
Split function is similar to the .net version of string, except that .net returns an array, and this returns a single column table, with each split substring occupying one row. Optionally, whether to remove space substrings and duplicate items. Similar functions on the market is not small, but most of them are in the loop to the original string changes, I feel that this is not good, although I do not know if the sql string is immutable like .net, but feel that try not to move the original string is best, in case the sql string is also immutable, that change once to generate a copy, especially each loop is changing, memory consumption is heartbreaking, so there is the idea of rebuilding a wheel.
Also, if SQL has CLR support turned on, it's perfectly possible to wrap a .net Split to use in SQL, which is easiest and should perform better (guessing). Without further ado, the function.
/*------------------------------- Function: Split String to Single Column Table v0.02 Author:AhDung Update:201403251158 -------------------------------*/ ALTER FUNCTION dbo.Split( @s VARCHAR(8000), - the string to split @separator NVARCHAR(10), --separator. Supports up to 10 character separators @removeEmpty BIT, --- Whether to remove the space item. Does not handle tabs, carriage returns, line feeds @unique BIT - whether to remove duplicate items ) RETURNS @t TABLE (S VARCHAR(500)) AS BEGIN IF @s IS NULL RETURN IF CHARINDEX(@separator,@s)=0 BEGIN INSERT @t VALUES(LEFT(@s,250)) RETURN END SET @s += @separator - make only one change to the original string. Actually, it works fine without changing it once, but you need to add judgment to the loop. This is to strike a balance between CPU and memory consumption DECLARE @lenS INT = LEN(@s),@lenSptr INT = DATALENGTH(@separator)/2,@i INT=0,@tmp NVARCHAR(250),@nextSptrIndex INT WHILE @i < @lenS BEGIN SET @nextSptrIndex=CHARINDEX(@separator,@s,@i+1) SET @tmp=SUBSTRING(@s,@i+1,@nextSptrIndex-1-@i) INSERT INTO @t VALUES(@tmp) SET @i+=DATALENGTH(@tmp)/2+@lenSptr END IF @removeEmpty=1 BEGIN DELETE @t WHERE S='' END IF @unique=1 BEGIN WITH cteA AS (SELECT ROW_NUMBER() OVER(PARTITION BY S ORDER BY S) AS 'ID' FROM @t) DELETE cteA WHERE ID<>1 END RETURN END