Reverse Statement Word by Word in SQL server

There are multiple ways to reverse a statement word by word in SQL Server. Here are some of the methods:

Method 1: Using STRING_SPLIT and STRING_AGG functions (SQL Server 2016 and later versions)

DECLARE @inputString VARCHAR(MAX) = 'This is a sample input string'

SELECT STRING_AGG(value, ' ') WITHIN GROUP (ORDER BY id DESC) AS reversedString
FROM (
    SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS id
    FROM STRING_SPLIT(@inputString, ' ')
) AS words

Explanation: - The input string is declared and assigned to the variable @inputString. - The STRING_SPLIT function is used to split the input string into individual words, and the ROW_NUMBER function is used to assign a unique ID to each word. - The outer query uses the STRING_AGG function to concatenate the words in reverse order, using the id column as the sorting criteria.

Output:

string input sample a is This

Method 2: Using XML and CROSS APPLY (SQL Server 2005 and later versions)

DECLARE @inputString VARCHAR(MAX) = 'This is a sample input string'

SELECT REVERSE(REPLACE(REPLACE((SELECT ' ' + word.value('.', 'VARCHAR(MAX)') AS [text()]
FROM (
    SELECT CAST('<w>' + REPLACE(@inputString, ' ', '</w><w>') + '</w>' AS XML) AS words
) AS t
CROSS APPLY words.nodes('/w') AS x(word)
FOR XML PATH('')), '&lt;', '<'), '&gt;', '>'), 1, 1, '')) AS reversedString

Explanation: - The input string is declared and assigned to the variable @inputString. - The input string is split into individual words using the REPLACE and XML functions, and the CROSS APPLY operator is used to apply the nodes method to each word. - The FOR XML PATH clause is used to concatenate the words into a single string, with a space delimiter. - The resulting string is reversed using the REVERSE function.

Output:

string input sample a is This

Method 3: Using a user-defined function (SQL Server 2005 and later versions)

CREATE FUNCTION dbo.ReverseWords (@inputString VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @outputString VARCHAR(MAX) = ''
    DECLARE @word VARCHAR(MAX) = ''
    DECLARE @pos INT = 1

    WHILE @pos <= LEN(@inputString)
    BEGIN
        IF SUBSTRING(@inputString, @pos, 1) = ' '
        BEGIN
            SET @outputString = ' ' + @word + @outputString
            SET @word = ''
        END
        ELSE
        BEGIN
            SET @word = @word + SUBSTRING(@inputString, @pos, 1)
        END

        SET @pos = @pos + 1
    END

    SET @outputString = @word + @outputString

    RETURN @outputString
END

Explanation: - A user-defined function named ReverseWords is created, which takes an input string and returns the reversed string. - The function uses a loop to iterate through each character in the input string, and builds the output string by concatenating each word in reverse order. - The function returns the reversed string.

Usage:

DECLARE @inputString VARCHAR(MAX) = 'This is a sample input string'

SELECT dbo.ReverseWords(@inputString) AS reversedString

Output:

string input sample a is This