• Another unorthodox method to get rid of repeating spaces:

    SELECT s.Sometext,

    (

    SELECT ' ' + LTRIM(RTRIM(Item))

    FROM dbo.Split(Sometext, ' ')

    WHERE Item > ''

    FOR XML PATH(''), TYPE

    ).value ('./text()[1]', 'varchar(max)')

    FROM #Sample s

    _____________
    Code for TallyGenerator