Is it possible to extract the multiple variable parts of data into different columns without a procedure? I'm currently trying to do an analsis of a table containing a couple thousand rows of data. I have managed to restrict the data to what I really need, but I'm not sure if I can split it up further without a procedure to help.

The table itself has an index on the column myCol and the rows contain different parts of data (example rows for MyCol):

MyText
MyText2\nX1: [S 1: 3] (S 3)\nX2: [S 1: 2] (S 2)\nX3: [S 75: 1] (S 75)Further text2
MyText3\nX2: [S 800: 3] (S 2400)Further text3


Now what I want is to get all rows that have X1 - X4 set. That is no problem:

Select myCol from myTable where myCol LIKE '%X_: [S%] (S %)%'


But then I want to split the data for X1-X4 into 1 output column each. As they can be at different positions within the column (and it is not necessary that all of them appear) the question is if this is doable without having to rely on a procedure (I have very limited permissions on that sql server so I have to avoid procedures where possible).
You should be able to do this with some string manipulation using charindex() and substring().

It looks like you can use X1: to find the start of the value you are looking for and ) to locate the end of a value.

declare @T table(MyCol varchar(100));

insert into @T values
('MyText'),
('MyText2\nX1: [S 1: 3] (S 3)\nX2: [S 1: 2] (S 2)\nX3: [S 75: 1] (S 75)Further text2'),
('MyText3\nX2: [S 800: 3] (S 2400)Further text3');

select substring(T.MyCol, P1.X1, P2.X1 - P1.X1 + 1) as X1,
       substring(T.MyCol, P1.X2, P2.X2 - P1.X2 + 1) as X2,
       substring(T.MyCol, P1.X3, P2.X3 - P1.X3 + 1) as X3,
       substring(T.MyCol, P1.X4, P2.X4 - P1.X4 + 1) as X4
from @T as T
  cross apply (
              select charindex('X1:', T.MyCol) as X1,
                     charindex('X2:', T.MyCol) as X2,
                     charindex('X3:', T.MyCol) as X3,
                     charindex('X4:', T.MyCol) as X4
              ) as P1
  cross apply (
              select case when P1.X1 &gt; 0 then charindex(')', T.MyCol, P1.X1) end as X1,
                     case when P1.X2 &gt; 0 then charindex(')', T.MyCol, P1.X2) end as X2,
                     case when P1.X3 &gt; 0 then charindex(')', T.MyCol, P1.X3) end as X3,
                     case when P1.X4 &gt; 0 then charindex(')', T.MyCol, P1.X4) end as X4
              ) as P2;


Result:

X1                   X2                       X3                     X4
-------------------- ------------------------ ---------------------- -----
NULL                 NULL                     NULL                   NULL
X1: [S 1: 3] (S 3)   X2: [S 1: 2] (S 2)       X3: [S 75: 1] (S 75)   NULL
NULL                 X2: [S 800: 3] (S 2400)  NULL                   NULL