Sql split delimited string into columns

See more:

SQL

Hi,
I Tried the below and i can get the frist 3 string split by _, but when i tried to 'FBC_asda_CB_All_Quota_A01' its not working.

can someone help me to split the 'FBC_asda_CB_All_Quota_A01'

Thanks in advance.

What I have tried:

with t as (select 'FBC_abc_CB' as val)
select t.*,
LEFT(val, charindex('_', val) - 1),
SUBSTRING(val, charindex('_', val)+1,
len(val) - CHARINDEX('_', reverse(val)) - charindex('_', val)),
REVERSE(LEFT(reverse(val), charindex('_', reverse(val)) - 1))
from t;