3
\$\begingroup\$

I have created the following user defined function in order to get the element at the position POSI (starting at 0) in the string EXPR. Each element is separated by a separator (SEP).

CREATE FUNCTION [dbo].[getStringElement_Expr_Pos_Sep]
( 
  @EXPR nvarchar(MAX),
  @POSI int,
  @SEP nchar
)
RETURNS nvarchar(MAX)
AS
BEGIN
  IF (@POSI < 0)
  BEGIN
    RETURN N'ERROR'
  END

  -- determine the occurrence of SEP in EXPR
  DECLARE @NB_SEP int
  SET @NB_SEP = LEN(@EXPR) - LEN(REPLACE(@EXPR, @SEP, ''))

  -- if there is no SEP -> return original EXPR
  IF (@NB_SEP = 0)
  BEGIN
    RETURN @EXPR
  END

  -- if POSI is higher than number of SEP -> return ERROR
  IF (@POSI > @NB_SEP)
  BEGIN
    RETURN N'ERROR'
  END

  DECLARE @i int = 0

  WHILE (@i < (@POSI))
  BEGIN
    SET @EXPR = SUBSTRING(@EXPR, LEN(LEFT(@EXPR, CHARINDEX(@SEP, @EXPR))) + 1, LEN(@EXPR))
    SET @i = @i + 1
  END

  IF (@POSI = @NB_SEP)
  BEGIN
    RETURN @EXPR
  END

  RETURN LEFT(@EXPR, CHARINDEX(@SEP, @EXPR) - 1)
END

For example, if I want to get the 3rd element (cc) in the string aaa|zzzzz|cc|mmmmm|e, I will call the UDF as below:

SELECT dbo.getStringElement_Expr_Pos_Sep(N'aaa|zzzzz|cc|mmmmm|e', 2, N'|')

Is there a more direct way to achieve the same result ? If not, are there things I could improve in my implementation ?

\$\endgroup\$

1 Answer 1

1
\$\begingroup\$

TL;DR

Your function seems fine and is suitable and is likely to perform well unless you pass in large expression pushing the varchar(max) limit. However, I would edit it to check if the first index in the expression is the separator since if it isn't, your function would return undesired results. For example, SELECT dbo.getStringElement_Expr_Pos_Sep(N'aaa|zzzzz|cc|mmmmm|e', 2, N'|') with your current function returns cc not zzzzz and calling it with 3 returns mmmmmm. So your function is 0 index based (not 1) which you stated, but isn't intuitive for the SQL language as most functions are 1 based. It also doesn't account for the first element being the separator (potentially)

Long Version

One alternative option would be to use a string splitter and avoid the while loop in your current function. So, using Jeff Moden's splitter located here where he explains some performance issues with other splitters, you could achieve your results as such:

declare @EXPR nvarchar(MAX),
        @POSI int,
        @SEP nchar

set @EXPR = '|zzzzz|cc|mmmmm|e'
set @POSI = 2
set @SEP = '|'

select * 
from dbo.DelimitedSplit8K(@EXPR,@SEP)
where ItemNumber = @POSI

Generally speaking, loops are slow in SQL Server and most RDMS. Also, as you can imagine, you can use the string splitter to bring back and entire table of elements which have a greater use case than your current function, reducing code maintenance. In this case, however, it depends on the length of your expression

Here is the test I ran:

set statistics time on

select * 
from dbo.DelimitedSplit8K('|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e','|')
where ItemNumber = 66

SELECT dbo.getStringElement_Expr_Pos_Sep('|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e|zzzzz|cc|mmmmm|e', 66, '|')

set statistics time off

Results in Avg

Position 66
Splitter = 3ms 
YourFun  = 6ms

Position 12
Splitter = 2ms 
YourFun  = 4ms

Position 3
AVG
Splitter = 2ms 
YourFun  = 3ms

Bias

However, if you are reduce the size of the expression to something small like your original use case, your function will run faster by a millisecond, so if you don't expect large expressions, or don't care about milliseconds, your function seems quite suitable.

Here is the full function

/****** Object:  UserDefinedFunction [dbo].[DelimitedSplit8K]    Script Date: 05/01/2017 9:42:33 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!

RETURNS TABLE WITH SCHEMABINDING AS
RETURN

/* "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
enough to cover VARCHAR(8000)*/

  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
GO
\$\endgroup\$
1
  • 1
    \$\begingroup\$ Thank you for your detailed answer. If the first element is the separator, I don't really know what I expect as result. An empty string as the first element doesn't seem to be a bad solution imo. Your alternative looks good anyway. :) \$\endgroup\$
    – Aleph0
    Commented May 2, 2017 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.