DECLARE @STR VARCHAR(100) = 'Hi how are you doing? I am good.' DECLARE @dev VARCHAR(1) = ' ' SELECT Split.a.value('.', 'VARCHAR(100)') AS E FROM ( SELECT Cast('<M> ' + Replace(Replace(@str, @dev, @dev + '</M><M>'), '.', '') + ' </M>' AS XML) AS yo ) a CROSS APPLY yo.nodes('/M') AS SPLIT(a)