Split a string using the parsename Function

-- Use the parsename function to split a string
Declare @ t table
(
Col varchar (80)
)

Insert into @ t
Select '1970 _ 12_124 'Union all
Select '2017 _ 125_1243'

Select Col, col1 = parsename (replace (COL, '_', '.'), 3), ---- replace '.' Because parsename only recognizes '.'
Col2 = parsename (replace (COL, '_', '.'), 2 ),
Col3 = parsename (replace (COL, '_', '.'), 1)
From @ t

Col col1 col2 col3
----------------------------------------
123_12_124 123 12 124
1234_125_1243 1234 125 1243

(The number of affected rows is 2)