Just for the fun of it... Performance is about the same as what Alan posted previously...

DECLARE

@String VARCHAR(250) = '123,346,abc,def,ghi,123,jkl,345,678,mno,901,234,567,pqr',

@Delimiter CHAR(1) = ',';

WITH

n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),

cte_Tally (n) AS (

SELECT TOP (DATALENGTH(@String))

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM n n1, n n2, n n3, n n4

),

cte_Split AS (

SELECT

t.N,

Val = SUBSTRING(@String, t.N, 1),

LagLead = CONCAT(SUBSTRING(@String, t.N - 1, 1), SUBSTRING(@String, t.N + 1, 1))

FROM

cte_Tally t

),

cte_Starts AS (

SELECT N = 0

UNION ALL

SELECT

s.N

FROM

cte_Split s

WHERE

s.Val = @Delimiter

AND TRY_CAST(s.LagLead AS INT) IS NULL

)

SELECT

ItemNumber = ROW_NUMBER() OVER (ORDER BY s.N),

Item = SUBSTRING(@String, s.n +1, ISNULL(LEAD(s.n, 1) OVER (ORDER BY s.N), 8000) - s.n -1)

FROM

cte_Starts s;