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.
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.
Reference: Pinal Dave (https://blog.sqlauthority.com)