SQL SERVER – Reverse String Word By Word Part 4

Earlier I wrote a blog post about SQL SERVER – Reverse String Word By Word. SQL Server Expert Sanjay Monpara and Yuri Petrov contributed to the blog post with their excellent script which I have blogged earlier. In this blog post we will see a similar script by SQL Expert Paul G. He has essentially sliced the string up by the passed delimiter (in this case space) and reconstructed the string the reverse order. It is a neat way to reverse word order with plain T-SQL. His script is the star of Reverse String Word By Word Part 4.

Here is the script by Paul:

DECLARE @String NVARCHAR(50)
DECLARE @Delimiter CHAR(1)
DECLARE @count INT
DECLARE @slice VARCHAR(8000)
DECLARE @rtnString NVARCHAR(50)
SET @Delimiter = ' '
SET @String = 'I am Pinal Dave'
SET @rtnString = ''
SET @count = 1
WHILE @count > 0
BEGIN
SET @count = CHARINDEX(@Delimiter,@String)
IF (@count > 0)
SET @slice = LEFT(@String,@count - 1)
ELSE
SET @slice = @String
IF(LEN(@slice) > 0)
SET @rtnString = @slice + ' ' + @rtnString
SET @String = RIGHT(@String,LEN(@String) - @count)
END
SELECT @rtnString

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 Paul for awesome contribution.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.