title: T-SQL split string containing alpha and numeric characters by variable delimiter
body: <p>I am trying to split a string that has 3 set Alpha Characters that can appear in any order followed by a numeric value. The issue I am having is that the order of the alpha characters isn't fixed. And neither is the number of numeric values after the alpha character it may contain any of the following examples:</p>
  
  <pre><code>X1Y45Z1
  Y25Z1
  X1Y9Z1
  X2Z6
  </code></pre>
  
  <p>With a a lot of help from our local IT ( I am still learning SQL) I have managed to separate out X Y and Z into separate columns with the numbers after them, but they don't always appear in order</p>
  
  <ul>
  <li>Col1 may contain X or Y </li>
  <li>Col2 may contain Y or Z</li>
  <li>Col3 may contain Z or nothing</li>
  </ul>
  
  <p>I am trying to get a result like the following:</p>
  
  <p>If X is in Col1, Show number(s) after X, in new column "X", if Y is in col1, Show number(s) after Y in new column "Y", etc.</p>
  
  <p>At present we are using 2 cte's to break up the string. and I am trying to simplify it so that I can search the string, have 3 columns after created 'X','Y','Z' and put the correct number(s) after each Alpha delimiter into it. I should note I <strong>Do Not</strong> have full admin access so I cannot create new tables or update/insert data or clean it. </p>
  
  <p>Also apologies if this is slightly formatted incorrectly. It is my first post on StackOverflow</p>
  
  <pre><code>declare @tbl table
  (
      Col1 varchar(100), &lt;-------This Column contains the values I want
  )
  
  insert into @tbl
      select Col1,
      from table1, 
      where xyz
  
  ;with cte as
  (
      select
          Col1,
          replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(**Col1**,'P', '&lt;/x&gt;&lt;x&gt;P'),'C', '&lt;/x&gt;&lt;x&gt;C'),'I', '&lt;/x&gt;&lt;x&gt;I'),'M', '&lt;/x&gt;&lt;x&gt;M'),'S', '&lt;/x&gt;&lt;x&gt;S'),'Q', '&lt;/x&gt;&lt;x&gt;Q'),'L', '&lt;/x&gt;&lt;x&gt;L'),'T', '&lt;/x&gt;&lt;x&gt;T'),'E', '&lt;/x&gt;&lt;x&gt;E'),'R', '&lt;/x&gt;&lt;x&gt;R'),'U', '&lt;/x&gt;&lt;x&gt;U'),'W', '&lt;/x&gt;&lt;x&gt;W')
   **Col1NODES**
      from 
          @tbl
  )
  , cte2 (Col1, Col1Nodes) as
  (
      select
          Col1,
          convert(xml,'&lt;z&gt;&lt;x&gt;' + Col1nodes + '&lt;/x&gt;&lt;/z&gt;') **Col1NODES**
      from 
          cte
  )
  select 
      Col1, 
      isnull(Col1Nodes.value('/z[1]/x[2]','varchar(100)'),'-') F1,
      isnull(Col1Nodes.value('/z[1]/x[3]','varchar(100)'),'-') F2,
      isnull(Col1Nodes.value('/z[1]/x[4]','varchar(100)'),'-') F3
  from 
      cte2
  </code></pre>
  
  <p>Current output is below:</p>
  
  <p><a href="https://i.stack.imgur.com/FT1qK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FT1qK.png" alt="enter image description here"></a></p>