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