title: SQL Split String on Delimiter and set to new columns
body: <p>There has been questions related to this asked, but I have not gotten the solution I am need of.</p>
  
  <p>The string(s) I am trying to split up look like this:</p>
  
  <pre><code>/Dev/act/billing
  </code></pre>
  
  <p>or</p>
  
  <pre><code>/ST/recManage/prod/form
  </code></pre>
  
  <p>The issue I have is the first '/' giving me problems when I try and do things with LEFT/RIGHT/SUBSTRING/CHARINDEX. It messes up counts and stopping at the delimiter. Also, it is important to note that the number of delimiters changes. So I want to find a way to split it up so I can get every possible substring.</p>
  
  <pre><code>RIGHT(c3.Path,CHARINDEX('/', REVERSE(c3.Path))-1) AS LastPath
  </code></pre>
  
  <p>This has gotten me the last part of the string. I have messed with other things ilke:</p>
  
  <pre><code>SUBSTRING(c3.Path,CHARINDEX('/',c3.Path,(CHARINDEX('/',c3.Path)+1))+1,len(c3.Path)),
  </code></pre>
  
  <p>This gets everything after the second '/'</p>
  
  <p>I have also messed with XML and</p>
  
  <pre><code>SET @delimiter='/' 
  ;WITH CTE AS
  
  (SELECT CAST('&lt;M&gt;' + REPLACE([Path], @delimiter , '&lt;/M&gt;&lt;M&gt;') + '&lt;/M&gt;' AS XML) 
          AS [Type XML]
  
  
  FROM  [Rpts].[dbo].[Cata] 
  )
  , 
  
  CTE2 as (Select [Type XML].value('/M[2]', 'varchar(50)') As FirstPath from CTE)
  </code></pre>
  
  <p>Then doing: CTE2.FirstPath to get the result. But this then gives NULL</p>
  
  <p>I am not on SQL 2016 so I cannot use SPLIT_STRING.</p>
  
  <p>Thank you </p>