Earlier I wrote a blog post about SQL SERVER – Reverse String Word By Word. SQL Server Expert Sanjay Monpara wrote a great comment to the blog post where he has proposed alternate solution. Right after the solution of the Sanjay, another expert Yuri Petrov suggested a modification to the script. In this blog post we will be seeing the solution proposed by Yuri Petrov in this blog post.
Yuri’s script is in two parts.
Part 1: Create a auxiliary table with numbers.
CREATE TABLE Nums (num INT PRIMARY KEY);
WITH
cteNums8000
AS (
SELECT TOP (8000) ROW_NUMBER () OVER (ORDER BY (SELECT 0)) AS [num] FROM sys.columns a
CROSS JOIN sys.columns b
)
INSERT INTO Nums
SELECT num FROM cteNums8000;
GO
Part 2: Script to Reverse String Words. If you want, you can convert this script to function afterwards.
DECLARE @ip VARCHAR (8000) = 'My Name is Pinal Dave';
WITH
cteSplit
AS (
SELECT SUBSTRING (@ip, num, CHARINDEX (' ', @ip + ' ', num) - num) AS [value], num
FROM Nums
WHERE num <= LEN (@ip)
AND SUBSTRING (' ' + @ip, num, 1) = ' '
)
SELECT STUFF ((SELECT ' ' + value
FROM cteSplit
ORDER BY num DESC
FOR XML path (''), TYPE).value ('.', 'varchar (8000)')
, 1, 1, '')
The above query will return results in the reverse order.
For example, as we have declared the variable as ‘My Name is Pinal Dave’, it will return results as ‘Dave Pinal is Name My’.
Thanks Yuri for awesome contribution.
Reference: Pinal Dave (https://blog.sqlauthority.com)
