Function to reverse string word by word in sql


1. First we need to create a table “tblNumbers”

CREATE TABLE tblNumbers (num INT PRIMARY KEY);
WITH
cteNumbers
AS (
SELECT TOP (8000) ROW_NUMBER () OVER (ORDER BY (SELECT 0)) AS [num]
FROM sys.columns a
CROSS JOIN sys.columns b
)
INSERT INTO tblNumbers
SELECT num FROM cteNumbers;


2. Scalar function to reverse string.

CREATE FUNCTION dbo.ReverseStringWordByWord
(	
	@string VARCHAR (8000)
)
RETURNS VARCHAR (8000)
AS
BEGIN
    Declare @ReverseString VARCHAR (8000);
	WITH
	cteStringArr
	AS (
	SELECT SUBSTRING (@string, num, CHARINDEX (' ', @string + ' ', num) - num) AS [value], num
	FROM tblNumbers
	WHERE num <= LEN (@string)
	AND SUBSTRING (' ' + @string, num, 1) = ' '
	)
	Select @ReverseString=STUFF ((SELECT ' ' + value
	FROM cteStringArr
	ORDER BY num DESC
	FOR XML path (''), TYPE).value ('.', 'varchar(8000)'), 1, 1, '')
	return @ReverseString
END