Reverse the order of words in TSQL

Question

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.

I think you could use the question you quoted - but use " " as the delimiter instead of comma. Once you have the split list it would be easy to manipulate the order. — Timbo, Oct 07 '11 at 08:41
Why not use the solution you already found? It was for a comma-separated string, but yours is space-separated, so the only difference is the delimiter (ignoring punctuation issues). More generally, this sort of problem is a common learning or tutorial exercise in many languages, so you might get more out of this task by trying to solve it yourself (in TSQL or a CLR procedure) and then post when you have a specific coding problem and you can show the code you already tried. — Pondlife, Oct 07 '11 at 08:44
Check this question for various solution to the **`SPLIT()`** function problem: http://stackoverflow.com/questions/2647/split-string-in-sql — ypercubeᵀᴹ, Oct 07 '11 at 08:45
The reason I haven't used the question I referenced is because I read that cursors in a production environment is a bad idea? I'm sure there must be an easier solution then the way posted there? — Apqu, Oct 07 '11 at 08:50
@Aracas - [See this article for a load of split functions](http://www.sommarskog.se/arrays-in-sql-2005.html) — Martin Smith, Oct 07 '11 at 09:04
@Aracas: Cursors have a bad reputation because most people use them to loop through SQL resultsets, doing things that could be solved better with a set-based solution. But I see no issues about using a cursor only on a few in-memory variables. — Christian Specht, Oct 07 '11 at 09:14

score 8 · Accepted Answer · answered Oct 07 '11 at 09:23

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

score 2 · Answer 2 · answered Aug 30 '16 at 04:14

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

Thanks John. As heads up, need to add +1 after len(@output), otherwise it cuts off a letter — figgy, Nov 08 '18 at 09:37

score 2 · Answer 3 · edited May 23 '17 at 12:08

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.

score 2 · Answer 4 · answered Oct 07 '11 at 14:04

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

user2484400 · Answer 5 · 2013-06-15T00:40:04.530

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, '')

Reverse the order of words in TSQL

5 Answers5

Linked

Related