Development

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:

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.

comments powered by Disqus