Is there a better way to parse value into multiple columns?-CodePudding

I have a value that needs to be parsed into a potential three values:

MasterUnit	Unit1	Unit2	Unit3
10ABC	10A	10B	10C
10AB	10A	10B	NULL

I'm accomplishing what I need in the below script. My question to you... Is there a better, more efficient way to do so (in fewer lines of code)?

cast([UnitNum] as char(5)) as MasterUnit,
left(cast([UnitNum] as char(5)), 3) as Unit1,
case
when (left(left(cast([UnitNum] as char(5)), 2)   right(cast([UnitNum] as char(5)), 2),3)) = left(cast([UnitNum] as char(5)), 2)
then NULL
else (left(left(cast([UnitNum] as char(5)), 2)   right(cast([UnitNum] as char(5)), 2),3))
end as Unit2,
case
when (left(cast([UnitNum] as char(5)), 2))   (right(cast([UnitNum] as char(5)), 1)) = left([UnitNum],2)
then NULL
else (left(cast([UnitNum] as char(5)), 2)   right(cast([UnitNum] as char(5)), 1))
end as Unit3

CodePudding user response：

With a little bit of string manipulation and a CROSS APPLY

Select A.* 
      ,Unit1 = S1 nullif(substring(S2,1,1),'')
      ,Unit2 = S1 nullif(substring(S2,2,1),'')
      ,Unit3 = S1 nullif(substring(S2,3,1),'')
 From  YourTable A
 Cross Apply ( values ( left(MasterUnit,patindex('%[^0-9]%',MasterUnit)-1),substring(MasterUnit,patindex('%[^0-9]%',MasterUnit),100) ) ) B(S1,S2)

Reults

CodePudding user response：

Making some assumptions about your sample data, perhaps something like the following using a cross apply to re-use the initial two characters is about as minimal as possible :

select MasterUnit,
    u   NullIf(Substring(MasterUnit,3,1),'') Unit1,
    u   NullIf(substring(MasterUnit,4,1),'') Unit2,
    u   NullIf(substring(MasterUnit,5,1),'') Unit3
from t
cross apply(values(Left(MasterUnit,2)))m(u);