Reverse the order of words in a string in SQL Server
I recently found myself with a situation in SQL Server where I had a string consisting of a series of values (constructed using the CLR function for concatenating values that I wrote about last week) that I needed to reverse. Unfortunately, it’s not as simple as using SQL Server’s built-in REVERSE function. That actually reverses each individual character in a string, rather than switching the order of words.
If you think about it, a word is basically a sequence of characters, delimited by a space (in its simplest form anyway). So, I set to work on a Scalar User-Defined Function (UDF) which could be used to reverse the order of any number of concurrent sequences in a string, separated by a defined delimiter. For my needs, my max field size was 200 characters but you can adjust this to whatever suits your needs. Here’s the function:[cc lang=”tsql”]
CREATE FUNCTION [dbo].[udf_ReverseSequenceOrder]
DECLARE @Output nvarchar(200)
WHILE LEN(@Input) > 0
IF CHARINDEX(@Delimiter, @Input) > 0
SET @Output = SUBSTRING(@Input,0,CHARINDEX(@Delimiter, @Input)) + @Delimiter + ISNULL(@Output,”)
SET @Input = SUBSTRING(@Input,CHARINDEX(@Delimiter, @Input)+1,LEN(@Input))
SET @Output = @Input + @Delimiter + ISNULL(@Output,”)
SET @Input = ”
So, if you take the following sequence:
and run it through the function with a space delimiter (‘ ‘):[cc lang=”tsql”]
SELECT * FROM [dbo].[udf_ReverseSequenceOrder] (‘just a small town girl living in a lonely world’,’ ‘)[/cc]
You’ll get the following output:
Simples. I’ve been using this on comma-delimited lists of numbers but it’ll work for anything with a delimiter of 5 characters or less. Again, as with everything in SQL, this may have a performance impact when performed on large datasets so make sure that this is the best route for your solution. CLR would be a perfectly viable option here again.