title: Parsing text to multiple columns
body: <p>I have a feed that is populating a single text field in a table with statistics.
  I need to pull this data into multiple fields in another table 
  but the strange format makes importing automatically difficult.</p>
  
  <p>The file format is flat text but an example is below:</p>
  
  <pre><code>08:34:52 Checksum=180957248,TicketType=6,InitialUserType=G,InitialUserID=520,CommunicationType=Incoming,Date=26-03-2012,Time=08:35:00,Service=ST,Duration=00:00:14,Cost=0.12
  </code></pre>
  
  <p>Effectively it's made up of:</p>
  
  <pre><code>[timestamp] [Field1 name]=[Field1 value],[Field2 name]=[Field2 value],[Field4 name]=[Field4 value]...[CR]
  </code></pre>
  
  <p>All fields are always in the same order but <strong>not always present</strong>.
  Total columns could be anywhere from 5 to 30.</p>
  
  <p>I've tried the below function to translate it which seems to work mostly but seems to randomly skip fields:</p>
  
  <p>Parsing the data:</p>
  
  <pre><code>(SELECT [Data].[dbo].[GetFromTextString] ( 'Checksum=' ,',' ,RAWTEXT)) AS RowCheckSum,
  (SELECT [Data].[dbo].[GetFromTextString] ( 'TicketType=' ,',' ,RAWTEXT)) AS TicketType,
  </code></pre>
  
  <p>And the Function:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[GetFromTextString]
  -- Input start and end and return value.
     (@uniqueprefix VARCHAR(100),
      @commonsuffix VARCHAR(100),
      @datastring VARCHAR(MAX) )
  RETURNS VARCHAR(MAX) -- Picked Value.
  AS
  BEGIN
  
      DECLARE @ADJLEN INT = LEN(@uniqueprefix)
  
      SET @datastring = @datastring + @commonsuffix
  
     RETURN ( 
      CASE WHEN (CHARINDEX(@uniqueprefix,@datastring) &gt; 0) 
           AND (CHARINDEX(@uniqueprefix + @commonsuffix,@datastring) = 0)
      THEN SUBSTRING(@datastring, PATINDEX('%' + @uniqueprefix + '%',@datastring)+@ADJLEN, CHARINDEX(@commonsuffix,@datastring,PATINDEX('%' + @uniqueprefix + '%',@datastring))- PATINDEX('%' + @uniqueprefix + '%',@datastring)-@ADJLEN) ELSE NULL END
  )
  END
  </code></pre>
  
  <p>Could anyone suggest a better/cleaner way to strip out the data or could someone work out why this formula skips rows?</p>
  
  <p>Any help really appreciated.</p>