As a side bar, for those interested in a Tally table solution that will work in virtually any release of SQL Server...
[font="Courier New"]--===== Build the test table as the data source
CREATE TABLE dbo.TableA (Column1 VARCHAR(5), Column2 VARCHAR(30))
INSERT INTO dbo.TableA
(Column1, Column2)
SELECT 'a1', '1:3:5:6' UNION ALL
SELECT 'a2', '2:4:5'
--===== Solution for virtually any version of SQL Server
INSERT INTO dbo.TableB
(Column1, Column2)
SELECT a.Column1,
SUBSTRING(a.Column2, t.N+1, CHARINDEX(':', a.Column2, N+1) - N-1) AS Column2
FROM dbo.Tally t
CROSS JOIN
(SELECT Column1, ':'+Column2+':' AS Column2 FROM dbo.TableA) a
WHERE N < LEN(a.Column2)
AND SUBSTRING(a.Column2, N, 1) = ':'
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.