You can use a Tally table and CROSS APPLY to scan your text and split for your delimiter.

DECLARE @t TABLE (col1 varchar(10), col2 varchar(30));

INSERT INTO @t

SELECT 'a1', '1:3:5:6'

UNION ALL SELECT 'a2', '2:4:5';

; WITH t1 (col1, col2) AS

(

SELECT

col1,

':' + col2 + ':'

FROM @t

)

SELECT

t1.col1,

t2.Item

FROM t1

CROSS APPLY

(

SELECT

SUBSTRING(t1.col2, N + 1, CHARINDEX(':', t1.col2, N + 1) - N - 1) Item

FROM Tally

WHERE N < LEN(t1.col2)

AND SUBSTRING(t1.col2, N, 1) = ':'

) t2;

If you don't know a Table or how to work with please search this site. You will find a really good article published by Jeff Moden which explains the Tally table and how to use it for requirements like this.

Flo