T-SQL split string containing alpha and numeric characters by variable delimiter

Question

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:

X1Y45Z1
Y25Z1
X1Y9Z1
X2Z6

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

• Col1 may contain X or Y
• Col2 may contain Y or Z
• Col3 may contain Z or nothing

I am trying to get a result like the following:

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.

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 Do Not have full admin access so I cannot create new tables or update/insert data or clean it.

Also apologies if this is slightly formatted incorrectly. It is my first post on StackOverflow

declare @tbl table
(
    Col1 varchar(100), <-------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', '</x><x>P'),'C', '</x><x>C'),'I', '</x><x>I'),'M', '</x><x>M'),'S', '</x><x>S'),'Q', '</x><x>Q'),'L', '</x><x>L'),'T', '</x><x>T'),'E', '</x><x>E'),'R', '</x><x>R'),'U', '</x><x>U'),'W', '</x><x>W')
 **Col1NODES**
    from 
        @tbl
)
, cte2 (Col1, Col1Nodes) as
(
    select
        Col1,
        convert(xml,'<z><x>' + Col1nodes + '</x></z>') **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

Current output is below:

Answer 1

If you have SQL Server 2016+ you may try to use the following solution, based on JSON. The important part is to transform the input data into a valid JSON object (X1Y45Z1 is transformed into {"X":1,"Y":45,"Z":1} for example). After that you need to parse this object with OPENJSON() function using the appropriate WITH clause to define the columns in the output.

Table:

CREATE TABLE Data (
   TextData nvarchar(100)
)
INSERT INTO Data
   (TextData)
VALUES
   ('X1Y45Z1'),
   ('Y25Z1'),
   ('X1Y9Z1'),
   ('X2Z6'),
   ('Z1X6')

Statement:

SELECT d.TextData, j.*
FROM Data d
CROSS APPLY OPENJSON(
   CONCAT(
      N'{',
      STUFF(REPLACE(REPLACE(REPLACE(d.TextData, N'X', N',"X":'), N'Y', N',"Y":'), N'Z', N',"Z":'), 1, 1, N''),
      N'}'
   )   
) WITH (
   X int '$.X',
   Y int '$.Y',
   Z int '$.Z'
) j

Output:

---------------------
TextData    X   Y   Z
---------------------
X1Y45Z1     1   45  1
Y25Z1           25  1
X1Y9Z1      1   9   1
X2Z6        2       6
Z1X6        6       1

For versions before SQL Server 2016, you may use an XML based approach. You need to transform text data into an appropriate XML (X1Y45Z1 is transformed into <row><name>X</name><value>1</value></row><row><name>Y</name><value>45</value></row><row><name>Z</name><value>1</value></row> for example):

SELECT
   TextData,
   XmlData.value('(/row[name = "X"]/value/text())[1]', 'nvarchar(4)') AS X,
   XmlData.value('(/row[name = "Y"]/value/text())[1]', 'nvarchar(4)') AS Y,
   XmlData.value('(/row[name = "Z"]/value/text())[1]', 'nvarchar(4)') AS Z
FROM (
   SELECT
      TextData, 
      CONVERT(
         xml, 
      CONCAT(
           STUFF(REPLACE(REPLACE(REPLACE(d.TextData, N'X', N'</value></row><row><name>X</name><value>'), N'Y', N'</value></row><row><name>Y</name><value>'), N'Z', N'</value></row><row><name>Z</name><value>'), 1, 14, N''),
           N'</value></row>'
         )
      ) AS XmlData
   FROM Data d
) x