T SQL Script - Splitting a delimited column value to multiple columns

Create Table Product(Col1 varchar(10),Col2 Varchar(1000))

iNSERT INTO pRODUCT(Col1,Col2) sELECT 1,'AR-5381 BA-8327 BB-7421 BB-8107 BB-9108'
iNSERT INTO pRODUCT(Col1,Col2) sELECT 2,'BC-M005 BC-R205 BE-2349 BE-2908 BK-M18B-40'

   
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)
SELECT Col1, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4, [5] AS Column5
FROM
(SELECT Col1,
        ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY nums.n) AS PositionInList,
        LTRIM(RTRIM(SUBSTRING(valueTable.Col2, nums.n, charindex(N' ', valueTable.Col2 + N' ', nums.n) - nums.n))) AS [Value]
 FROM   Numbers AS nums INNER JOIN Product AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Col2)) AND SUBSTRING(N' ' + valueTable.Col2, n, 1) = N' ') AS SourceTable
PIVOT
(
MAX([VALUE]) FOR PositionInList IN ([1], [2], [3], [4], [5])
) AS Table2

Drop table Product