Split strings in a column based on text values and numerical values such as patindex-CodePudding

I have a column that displays stock market options data like below:

GME240119C00020000
QQQ240119C00305000
NFLX240119P00455000

I want to be able to split these up so they show up like:

GME|240119|C|00020000
QQQ|240119|C|00305000
NFLX|240119|P|00455000

I was able to split the first portion with the ticker name by using the code below, but I don't know how to split the rest of the strings.

case patindex('%[0-9]%', str)
    when 0 then str
    else left(str, patindex('%[0-9]%', str) -1 ) 
end
from t

edit: for anyone who is wondering, I used Dale's solution below to get my desired outcome. I edited the query he provided to make the parts show up as individual columns

select
    substring(T.contractSymbol,1,C1.Position-1) as a 
    ,substring(T.contractSymbol,C1.Position,6) as b
    ,substring(S1.Part,1,1) as c
    ,substring(S1.Part,2,len(S1.Part)) as d
from Options_Data_All T
cross apply (
    values (patindex('%[0-9]%', T.contractSymbol))
) C1 (Position)
cross apply (
    values (substring(contractSymbol, C1.Position 6, len(T.contractSymbol)))
) S1 (Part);

CodePudding user response：

Just keep doing what you started doing by using SUBSTRING. So as you did find the first number and actually in your case, based on the data provided, everything else is fixed length, so you don't have to search anymore, just split the string.

declare @Test table (Contents nvarchar(max));

insert into @Test (Contents)
values
('GME240119C00020000'),
('QQQ240119C00305000'),
('NFLX240119P00455000');

select
    substring(T.Contents,1,C1.Position-1)   '|'   substring(T.Contents,C1.Position,6)   '|'   substring(S1.Part,1,1)   '|'   substring(S1.Part,2,len(S1.Part))
from @Test T
cross apply (
    values (patindex('%[0-9]%', T.Contents))
) C1 (Position)
cross apply (
    values (substring(Contents, C1.Position 6, len(T.Contents)))
) S1 (Part);

Returns:

Data
GME\|240119\|C\|00020000
QQQ\|240119\|C\|00305000
NFLX\|240119\|P\|00455000

If one can assume that all but the first column are fixed width then a simple SUBSTRING solution would suffice e.g.

select 
    substring(Contents,1,len(Contents)-15)
      '|'   substring(Contents,len(Contents)-14,6)
      '|'   substring(Contents,len(Contents)-8,1)
      '|'   substring(Contents,len(Contents)-7,8) [Data]
from @Test;

Note: CROSS APPLY is just a fancy way to use a sub-query to avoid needing to repeat a calculation.