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):

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).

1 Answer 1


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
('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 > 0 then charindex(')', T.MyCol, P1.X1) end as X1,
                     case when P1.X2 > 0 then charindex(')', T.MyCol, P1.X2) end as X2,
                     case when P1.X3 > 0 then charindex(')', T.MyCol, P1.X3) end as X3,
                     case when P1.X4 > 0 then charindex(')', T.MyCol, P1.X4) end as X4
              ) as P2;


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
  • The \n could also be used to split the data. Seems to have been used a separator (but missing in the last part, before the "further text"). Jan 11, 2016 at 9:40
  • In this case I omitted the \n before Further text as I wasn't and still am not sure that it will be there every time (the entries are generated by a different program that is not very lets say it so...consistent with \n when and how many it sends). so the looking for the first ) ater the beginning is very nice for me)
    – Thomas
    Jan 11, 2016 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.