cool hit counter [SQL] sql version of Split function. For splitting strings into single column tables_Intefrankly

[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

Man Bi!


Recommended>>
1、Is it a blessing or a curse Bitcoins Lightning Network Under Attack
2、Kashida Robotics May Collaboration Continues
3、In Oracle how to backup control files How many ways are there to back up control files
4、Another new look at Audis moon landing
5、Apple AI CEO reveals new advances in selfdriving car technology or becomes an employee pickup car

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号