The build-in function REVERSE just reverse the characters of a word or text. It does not reverse the word order of a string. So, If you want to reverse the order of words in a string, this simple t-sql function can be used. It will return reversed word order of given string.

Code Snippet

CREATE FUNCTION [dbo].[fn_ReverseWordOrder]
(
	@Input VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)  
BEGIN
        
   DECLARE @Output VARCHAR(MAX)
   SET  @Output = ''  
   
	DECLARE @Lenght INT 
	
	WHILE LEN(@Input) > 0
	BEGIN
		IF CHARINDEX(' ', @Input) > 0
		BEGIN
			SET @Output = SUBSTRING(@Input,0,CHARINDEX(' ', @Input)) + ' ' + @Output
			SET @Input = LTRIM(RTRIM(SUBSTRING(@Input,CHARINDEX(' ', @Input) + 1,LEN(@Input))))
		END
		ELSE
		BEGIN
			SET @Output = @Input + ' ' + @Output
			SET @Input = ''
		END
	END
	      
   RETURN @Output
   
END

Sample Usage

SELECT dbo.[fn_ReverseWordOrder]('Reverse the Order of Words in T-SQL')
--Output : T-SQL in Words of Order the Reverse 

Note

There is another simple function to get same result. But It need split function. Here is the code snippet. You can use this one as well.

CREATE FUNCTION [dbo].[fn_ReverseWordOrder]
(
	@Input VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)  
BEGIN
        
   DECLARE @Output VARCHAR(MAX)
   SET  @Output = ''  
	
   SELECT @Output =  @Output + ' ' + Data FROM fn_Split(@Input,' ') Order By ID DESC
	      
   RETURN @Output
   
END




author's photo

Author : Blue Cloud

Just a normal software developer who love to write code. Developing professional software since 1999. After working in the software industry for many years, I've started my own website to share knowledges and experiences.

COMMENTS
blog comments powered by Disqus