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.