sql server - Reverse the order of words in T-SQL

Question

I would like to know how (if it is possible) to reverse the order of words returned from a T-SQL string (varchar).

I know about the T-SQL REVERSE function. but the function also reverses the letters in the word for example:

Input > We want to tell you we all love StackOverflow
Output > wolfrevOkcatS evol lla ew uoy llet ot tnaw eW

I want to actually achieve the following in T-SQL:

Input > We want to tell you we all love StackOverflow
Output > Stackoverflow love all we you tell to want We

The only slightly similar question I found anywhere was this one, however that includes splitting comma-separated strings which I do not need to do.

I'm sure there is a way to achieve the above, even if it's a custom function or SQL-CLR function.

---

I managed to split my string up using the following:

-- Create a space delimited string for testing
declare @str varchar(max)
select @str = 'We want to tell you we all love StackOverflow'
-- XML tag the string by replacing spaces with </x><x> tags
declare @xml xml
select @xml = cast('<x><![CDATA['+ replace(@str,' ',']]></x><x><![CDATA[') + ']]></x>' as xml)
-- Finally select values from nodes <x> and trim at the same time
select ltrim(rtrim(mynode.value('.[1]', 'nvarchar(50)'))) as Code
from (select @xml doc) xx
cross apply doc.nodes('/x') (mynode)

The problem now is trying to piece it all back together into one string in a backwards (DESC) order.

Answer 1

You can create one small function in SQL to reverse a string like below:

DECLARE @source VARCHAR(MAX)
DECLARE @dest VARCHAR(MAX)
DECLARE @lenght INT

SET @source = 'We want to tell you we all love StackOverflow'
SET @dest = ''

WHILE LEN(@source) > 0
BEGIN
    IF CHARINDEX(' ', @source) > 0
    BEGIN
        SET @dest = SUBSTRING(@source,0,CHARINDEX(' ', @source)) + ' ' + @dest
        SET @source = LTRIM(RTRIM(SUBSTRING(@source,CHARINDEX(' ', @source)+1,LEN(@source))))
    END
    ELSE
    BEGIN
        SET @dest = @source + ' ' + @dest
        SET @source = ''
    END
END
SELECT @dest