"SQL" SQL version of the Split function. Used to split a string into a single-column table

Original: "SQL" SQL version of the Split function. Used to split a string into a single-column table

Functionality is similar to the. NET version of the String.Split function, except that. NET returns an array, which returns a single-column table with one row for each split substring. Optional whether to remove empty lattice strings and duplicates. Similar functions on the market are not small, but mostly in the loop to change the original string, I feel that this is not good, Although it is not known that the SQL string is not as immutable as. NET, but feel as far as possible not to move the original string is the best, in case the SQL string is also immutable, that change will produce a copy, especially every cycle is changing, memory consumption makes a person distressed, so there is the idea of re-build a wheel.

In addition, if SQL turns on CLR support, it can encapsulate a. NET split into SQL, which is the simplest and better (guess). No nonsense, on the function:

/*-------------------------------function: Splitting a string into a single-column table v0.02author:ahdungupdate:201403251158---------------- ---------------*/ALTER FUNCTIONdbo. Split (@s VARCHAR(8000),--the string to split@separator NVARCHAR(Ten),--The separator character. Maximum 10-character delimiter support@removeEmpty BIT,--whether to remove the space item. Do not handle tab, carriage return line@unique BIT                   --whether to remove duplicates)RETURNS @t TABLE(SVARCHAR( -)) asBEGINIF @s  is NULL RETURN    IF CHARINDEX(@separator,@s)=0  BEGIN INSERT @t VALUES( Left(@s, -))RETURN END    SET @s += @separator --make only one change to the original string. In fact, do not change at one time, but need to add judgment in the loop. This is to balance the CPU and memory consumption  DECLARE @lenS INT = LEN(@s),@lenSptr INT = datalength(@separator)/2,@i INT=0,@tmp NVARCHAR( -),@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 WHERES="'  END  IF @unique=1  BEGIN   withCteA as(SELECTRow_number () Over(PARTITION bySORDER  byS as 'ID'  from @t)  DELETECteAWHEREId<>1  END  RETURNEND

Wenbi!

"SQL" SQL version of the Split function. Used to split a string into a single-column table