CODEWITHSUNDEEP
sqlsql-server
DigCamara
DigCamara

Reputation: 5568

Seeking explanation for a MS SQL Function

I have the following SQL function in the database:

ALTER FUNCTION [dbo].[Split_Using] (@StringToSplit varchar(8000),
@Separator varchar(128))
RETURNS TABLE
AS
  RETURN
  WITH Indices
  AS (SELECT
    0 Start,
    1 Ends
  UNION ALL
  SELECT
    Ends,
    CHARINDEX(@Separator, @StringToSplit, Ends) + LEN(@Separator)
  FROM Indices
  WHERE Ends > Start)
  SELECT
    SUBSTRING(@StringToSplit, Start, CASE
      WHEN Ends > LEN(@Separator) THEN Ends - Start - LEN(@Separator)
      ELSE LEN(@StringToSplit) - Start + 1
    END) Id
  FROM Indices
  WHERE Start > 0

Even though I already know what it returns (separates a string into chunks, defined by the dividing string, e.g. Split_Using('a,b,c',',') would return a table with ('a' 'b' 'c') I would like to know:

  1. Can anyone explain how it does this?
  2. Where/how would I find the corresponding documentation (preferably on Microsoft's sites)
  3. Is this standard SQL?

Upvotes: 2

Views: 40

Answers (1)

Ed B
Ed B

Reputation: 796

This is using a recursive common table expression (CTE). In this case the WITH Indices part. The 'anchor' expression is the first part before the UNION ALL, the second part refers back to Indices recursively.

The MSDN documentation for these is here: https://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx

Upvotes: 4

Related Questions