select
T.*
, case when CA1.pos > 0 then left(T.LicenseNumber, CA1.pos-1) else T.LicenseNumber end
+ '-'
+ case when len(CA3.YearPart) =2 then '20' + CA3.YearPart
else CA3.YearPart
end as CombinedParts
from YourTable T
cross apply (
select charindex('-', T.LicenseNumber)
) CA1 (pos)
cross apply (
select case when CA1.pos > 0 then substring(T.LicenseNumber, CA1.pos+1, 50) end -- 50 is an example, use max length of field
) CA2 (DatePart)
cross apply (
select case when charindex('-',CA2.DatePart) > 0 then substring(CA2.DatePart, charindex('-',CA2.DatePart)+1, 50) else CA2.DatePart end
) CA3 (YearPart)
nb: I use CROSS APPLYs so that I can reuse the alias in subsequent calculations
Do NOT store it that way at allI didn't store this data. it was giving like this =(
case when CA1.pos > 0 then left(T.LicenseNumber, CA1.pos-1) else T.LicenseNumber end
+ '-'
+ case when len(CA3.YearPart) =2 then '20' + CA3.YearPart
else case when len(CA3.YearPart) =6 then right(ca3.YearPart,4) else CA3.YearPart end
end
Open in new window
Then if you are good with result.
Open in new window