title: Split String With Multiple Delimiters and Retain Delimiters
body: <p>I need to split a string into rows at specific word boundaries. The catch is that I need to maintain the specific word boundary which triggered the split because later, I want to recombine the rows and see the delimiter. I'm updating an existing function which looks something like this:</p>
  
  <pre><code>DECLARE @Input VARCHAR(8000) = 'AC/DC, The Quick, Brown Fox'
  DECLARE @Delimiters varchar(100) = '%[ ,-/]%'
  
  ;WITH 
      [Elements] AS
      (
          SELECT
              1 AS Position
              , 1 AS StartOffset
              , PATINDEX(@Delimiters, @Input) - 1 AS EndOffset
              , @Input AS Input
              , SUBSTRING(@Input, 1, ISNULL(NULLIF(PATINDEX(@Delimiters, @Input), 0) - 1, 8000)) AS Word
          UNION ALL
          SELECT 
              Position + 1 AS Position
              , EndOffset + 2 AS StartOffset
              , EndOffset + ISNULL(NULLIF(PATINDEX(@Delimiters, SUBSTRING(Input, EndOffset + 2, 8000)), 0), LEN(@Input) - EndOffset) AS EndOffset
              , Input
              , SUBSTRING(Input, EndOffset + 2, ISNULL(NULLIF(PATINDEX(@Delimiters, SUBSTRING(Input, EndOffset + 2, 8000)), 0) - 1, 8000)) AS Word
          FROM 
              [Elements]
          WHERE 
              EndOffset BETWEEN 1 AND LEN(@Input) - 1
      )
  SELECT
      *
  FROM
      [Elements]
  </code></pre>
  
  <p>Giving me:</p>
  
  <pre><code>+----------+-------------+-----------+-----------------------------+-------+
  | Position | StartOffset | EndOffset |            Input            | Word  |
  +----------+-------------+-----------+-----------------------------+-------+
  |        1 |           1 |         2 | AC/DC, The Quick, Brown Fox | AC    |
  |        2 |           4 |         5 | AC/DC, The Quick, Brown Fox | DC    |
  |        3 |           7 |         6 | AC/DC, The Quick, Brown Fox |       |
  |        4 |           8 |        10 | AC/DC, The Quick, Brown Fox | The   |
  |        5 |          12 |        16 | AC/DC, The Quick, Brown Fox | Quick |
  |        6 |          18 |        17 | AC/DC, The Quick, Brown Fox |       |
  |        7 |          19 |        23 | AC/DC, The Quick, Brown Fox | Brown |
  |        8 |          25 |        27 | AC/DC, The Quick, Brown Fox | Fox   |
  +----------+-------------+-----------+-----------------------------+-------+
  </code></pre>
  
  <p>This breaks it up nicely but omits the "/" and "," rows from the result set. I do have a Numbers table I can bump against and I'm pretty flexible about how this can be accomplished.</p>
  
  <p>I COULD brute force my way through it with a loop but this seems too barbaric.</p>