Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement!

split¶

SELECT
    A.state,
    split.A.value('.', 'VARCHAR(100)') AS String
FROM (
    SELECT 
        state,  
        CAST('<M>' + REPLACE(city, ',', '</M><M>') + '</M>' AS XML) AS string  
    FROM
        TableA
    ) AS A
CROSS APPLY String.nodes ('/M') AS split(a)