title: Split a string with multiple delimiters into 3 parts
body: <p>I have a string read in from a xml file. I need to split it into 3 parts. I need to run this query in a select statement for an insert query.
  <strong>UPDATE</strong>
  I am suppose to use this in a select query for a insert statement.</p>
  
  <p>Insert to table1 (col1,col2,company,station,location,coln)
     select (here i want this query for each columns. )</p>
  
  <p>String examples:</p>
  
  <pre><code>@declare exValue1 nvarchar(100) = 'Tempo &amp;gt; XNX (Marc) &amp;gt; Stores/Parts';
  @declare exValue2 nvarchar(100) = 'Sedan 12 &amp;gt; XNX (Peter Inc) &amp;gt; Stores/Inventory'; 
  @declare @company varchar(25);
  @declare @station varchar(25);
  @declare @location varchar(50);
  </code></pre>
  
  <p><strong>Delimiter</strong> is the 4 characters and its always same.</p>
  
  <p>For example 1st string, I need to split and assign </p>
  
  <pre><code> Tempo to company, XNX (Marc) to station, Stores/Parts to location.
  </code></pre>
  
  <p>For example 2nd string</p>
  
  <pre><code>Sedan 12 to company, XNX (Peter Inc) to station, Stores/Inventory to location.
  </code></pre>
  
  <p>I tried <code>substring</code> with <code>charindex</code> but I could only get 1st and 2nd string but I couldn't get the location string exactly. Any help appreciated TIA.</p>
  
  <pre><code>select @company = SUBSTRING(@exValue1, 1, CHARINDEX('&amp;gt;', @test) - 1)
  
  select @station = SUBSTRING (@exValue1, CHARINDEX('&amp;gt;', @test) + 4, LEN(@test))
  </code></pre>
  
  <p>I can't figure the location and station properly.</p>