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:
CREATE FUNCTION [dbo].[udf_ReverseSequenceOrder] ( @Input nvarchar(200) ,@Delimiter nvarchar(5) ) RETURNS nvarchar(200) AS BEGIN DECLARE @Output nvarchar(200) WHILE LEN(@Input) > 0 BEGIN IF CHARINDEX(@Delimiter, @Input) > 0 BEGIN SET @Output = SUBSTRING(@Input,0,CHARINDEX(@Delimiter, @Input)) + @Delimiter + ISNULL(@Output,") SET @Input = SUBSTRING(@Input,CHARINDEX(@Delimiter, @Input)+1,LEN(@Input)) END ELSE BEGIN SET @Output = @Input + @Delimiter + ISNULL(@Output,") SET @Input = " END END RETURN SUBSTRING(@Output,0,LEN(@Output)) END
So, if you take the following sequence:
just a small town girl living in a lonely world and run it through the function with a space delimiter (' ‘):
SELECT * FROM [dbo].[udf_ReverseSequenceOrder] ('just a small town girl living in a lonely world',' ')
You’ll get the following output:
world lonely a in living girl town small a just.
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.