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;

-- first make a temp table to hold the sample data
declare @tmp table (
  ColumnA char(2),
  ColumnB varchar(50),
  TmpCol XML NULL)  --<<<<< NOTE the new column being added
 
-- put the sample data into the temp table.
-- NOTICE how this makes it so much easier for people to just copy and start testing
insert into @tmp (ColumnA, ColumnB)
select 'a1', '1:3:5:6' UNION ALL
select 'a2', '2:4:5'

-- update the xml column by replacing the delimiter with XML tags,
-- and putting the appropriate XML tags around the string.
update @tmp
   set TmpCol = '<rows><row>' + replace(ColumnB, ':', '</row><row>') + '</row></rows>'

-- shred the xml data apart into individual rows
select T.ColumnA,
       x.data.value('.','int') AS ColumnB
  --INTO TABLE2
  from @tmp T
  CROSS APPLY TmpCol.nodes('/rows/row') AS x(data)