By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable(name nvarchar(max))
insert into mytable
values
('ABC_DEFG_HIJKL'),
('A_B_C'),
('A_B_C_D')
3 rows affected
select name,
json_value(obj, '$[0]') name1,
json_value(obj, '$[1]') name2,
json_value(obj, '$[2]') name2,
json_value(obj, '$[3]') name4
from mytable t
cross apply (values('["' + replace(name, '_', '", "') + '"]')) x(obj)
| name | name1 | name2 | name2 | name4 |
|---|---|---|---|---|
| ABC_DEFG_HIJKL | ABC | DEFG | HIJKL | null |
| A_B_C | A | B | C | null |
| A_B_C_D | A | B | C | D |