title: Best way to extract segments / values from VARCHAR field in SET based SQL
body: <p>Take the following example data:</p>
  
  <pre><code>SELECT 'HelpDesk Call Reference F0012345, Call Update, 40111' AS [Subject]
  UNION ALL
  SELECT 'HelpDesk Call Reference F0012346, Call Resolved, 40112' AS [Subject]
  UNION ALL
  SELECT 'HelpDesk Call Reference F0012347, New call logged, 40113' AS [Subject]
  </code></pre>
  
  <p>What i would like to do is extract this data as follows:</p>
  
  <p><img src="https://i.stack.imgur.com/zfpUN.jpg" alt="This is how i need to select the data"></p>
  
  <p>As you can see, i need to extract the Ref, Type &amp; OurRef as seperate columns to ensure efficient set based SQL when processing the resulting emails.</p>
  
  <p>Usually for this scenario i would use a function such as this:</p>
  
  <pre><code>CREATE FUNCTION dbo.fnParseString (
      @Section SMALLINT ,
      @Delimiter CHAR ,
      @Text VARCHAR(MAX)
  )
  RETURNS VARCHAR(8000)
  AS 
      BEGIN
          DECLARE @NextPos SMALLINT;
          DECLARE @LastPos SMALLINT;
          DECLARE @Found SMALLINT;
  
          SELECT  @NextPos = CHARINDEX(@Delimiter, @Text, 1) ,
                  @LastPos = 0 ,
                  @Found = 1
  
          WHILE @NextPos &gt; 0
              AND ABS(@Section) &lt;&gt; @Found 
              SELECT  @LastPos = @NextPos ,
                      @NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1) ,
                      @Found = @Found + 1
  
          RETURN LTRIM(RTRIM(CASE
              WHEN @Found &lt;&gt; ABS(@Section) OR @Section = 0 THEN NULL
              WHEN @Section &gt; 0 THEN SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END)
              ELSE SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END)
          END))
      END
  </code></pre>
  
  <p>For example i then replace the white space before the ref to include a comma and split as follows:</p>
  
  <pre><code>WITH    ExampleData
            AS ( SELECT   'HelpDesk Call Reference F0012345, Call Update, 40111' AS [Subject]
                 UNION ALL
                 SELECT   'HelpDesk Call Reference F0012346, Call Resolved, 40112'
                 UNION ALL
                 SELECT   'HelpDesk Call Reference F0012347, New call logged, 40113'
               )
      SELECT  dbo.fnParseString(2, ',', REPLACE([Subject], 'HelpDesk Call Reference ', 'HelpDesk Call Reference, ')) AS [Ref] ,
              dbo.fnParseString(3, ',', REPLACE([Subject], 'HelpDesk Call Reference ', 'HelpDesk Call Reference, ')) AS [Type] ,
              dbo.fnParseString(4, ',', REPLACE([Subject], 'HelpDesk Call Reference ', 'HelpDesk Call Reference, ')) AS [OurRef]
      FROM    ExampleData
  </code></pre>
  
  <p>As you can see, i have a solution that gets the end-result i'm after, but the use of a messy udf isnt ideal &amp; i was wondering if <a href="/questions/tagged/sql-server" class="post-tag" title="show questions tagged &#39;sql-server&#39;" rel="tag">sql-server</a> has a better way of doing stuff like this - perhaps inline regular expressions? I.e. i think <code>PATINDEX()</code> accepts regular expressions as a search string - this in conjunction with <code>SUBSTRING()</code> could do what i need but i dont really know where to start?</p>
  
  <p><strong>Edit:</strong> Please note that this is a simplified example, the subject is variable and i'll also be adapting the same technique to parse the body, the body will have 8 items of data that i need to parse out using a variety of delimiters, so this rules out the use of <code>ParseName()</code> as it only allows 4 parts, and i can’t use fixed length (i.e. <code>substring()</code>) as the length will be very varied (especially if different helpdesks are involved (which they are) - this is why i was thinking along the lines of <code>PATINDEX()</code> &amp; <code>SUBSTRING()</code> </p>