DECLARE @CardNo VARCHAR(4000)='524545,36545,514584,254154,545654,2546,6545,25642,5415'
SELECT B.CardNo
FROM(
SELECT CardNo = CONVERT(xml,'<root><v>' + REPLACE(@CardNo, ',', '</v><v>') + '</v></root>')
) A OUTER APPLY
(
SELECT CardNo = N.v.value('.', 'varchar(100)')
FROM A.CardNo.nodes('/root/v') N(v)
) B