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 gaming blog if you're into that sort of thing.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

1 Comment

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