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] (
@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

[/cc]

 

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 (‘ ‘):

[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:

‘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.

Solutions Architect at Indicia. More years than I care to remember wrangling SQL Server and .NET, now mainly AWS. Data geek, football fan, and Xbox gamer. Check out my other blog Press B To Parent if you’re a gamer.

Leave a Reply

1 Comment

Next ArticleSQL Server Job Agent fails SSIS package with error: Option "/CALLERINFO" is not valid