Reverse the order of words in TSQL

问题:

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

I know about the TSQL REVERSE function but the 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 TSQL:

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, any help would be much appreciated.

Edit:

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.

回答1:

You can create one small function in SQL to reverse 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

回答2:

Firstly, this is one of those cases where a CLR-based solution is going to be better, especially in terms of performance (the solution will invariable make use of iterations and string manipulation).

Here's a few lines of C# that achieves the result, although it has none of the SQL-CLR code you will need:

string original = "We want to tell you we all love StackOverflow";
string[] parts = original.Split(' ');

StringBuilder reversedString = new StringBuilder();
for (int i = parts.Length - 1; i >= 0; i--)
{
    reversedString.Append(parts[i]);
    reversedString.Append(" ");
}

string result = reversedString.ToString();

It would be even shorter with LINQ, but I thought I'd keep it simple. For a T-SQL solution, you can start with the splitString function in vzczc's post in the split post already mentioned.

Based on that, you do this:

DECLARE @ReverseString VARCHAR(MAX)

SET @ReverseString = ''

SELECT @ReverseString = @ReverseString + s + ' ' 
FROM
(
    SELECT s, zeroBasedOccurance
    FROM dbo.SplitString('We want to tell you we all love StackOverflow', ' ')
) A
ORDER BY A.zeroBasedOccurance DESC

SELECT @ReverseString

This should work, but the method it uses for concatenating the string back together is undocumented and may not work correctly in future versions of SQL Server. It will probably also perform very badly when compared to a CLR solution, so if you have the time to implement that, do so.

回答3:

declare @str varchar(100),
        @result varchar(100)

set @str = 'We want to tell you we all love StackOverflow'

;with cte as 
(
select 1 pos_from, charindex(' ', @str) + 1 pos_to
union all
select pos_to, charindex(' ', @str + ' ', pos_to) + 1
from cte
where pos_to <= len(@str)
)
select @result = coalesce( @result + ' ', '') +substring(@str, pos_from, pos_to - pos_from - 1) 
from cte
order by pos_to desc

select @result

回答4:

SQL Server’s native XML support allows for some powerful (and concise) solutions to these sorts of problems:

DECLARE @xml XML, @source VARCHAR(MAX), @delimiter VARCHAR(10)

SET @source = 'We want to tell you we all love StackOverflow'
SET @delimiter = ' '
SET @xml = CAST(('<X>' + REPLACE(@source, @delimiter, '</X><X>') + '</X>') AS XML)

SELECT STUFF((SELECT @delimiter + C.value('.', 'VARCHAR(50)')
                FROM @xml.nodes('X') AS X(C)
               ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT 0)) DESC
                 FOR XML PATH('')), 1, 1, '')

回答5:

Since the accepted answer is not a function, here is one.

Also I removed the extra useless space generated by the accepted answer

CREATE FUNCTION [dbo].[fn_ReverseWords] (@input VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
    DECLARE @output VARCHAR(MAX)
    SET @output = ''
    WHILE LEN(@input) > 0
    BEGIN
        IF CHARINDEX(' ', @input) > 0
        BEGIN
            SET @output = SUBSTRING(@input,0,CHARINDEX(' ', @input)) + ' ' + @output
            SET @input = LTRIM(RTRIM(SUBSTRING(@input,CHARINDEX(' ', @input) + 1,LEN(@input))))
        END
        ELSE
        BEGIN
            SET @output = @input + ' ' + @output
            SET @input = ''
        END
    END
    RETURN substring(@output,0, len(@output)) -- remove useless space
END