Earlier I wrote a blog post where I have reversed the entire string which was passed to the function. You can read that over here User Defined Functions (UDF) to Reverse String. In that blog post I wrote a function which would reverse an entire string. However, recently I read a question in SQLBangalore where the user wanted to reverse string, but wanted to keep all the words in the same order. For example,
If the string is – “I am Pinal Dave”, it should be reversed as “Dave Pinal am I.”
Well, here is the function which does the same task.
CREATE FUNCTION [dbo].[fn_ReverseWordsInSentence] ( @ip VARCHAR(MAX) ) RETURNS VARCHAR(MAX) BEGIN DECLARE @op VARCHAR(MAX) SET @op = '' DECLARE @Lenght INT WHILE LEN(@ip) > 0 BEGIN IF CHARINDEX(' ', @ip) > 0 BEGIN SET @op = SUBSTRING(@ip,0,CHARINDEX(' ', @ip)) + ' ' + @op SET @ip = LTRIM(RTRIM(SUBSTRING(@ip,CHARINDEX(' ', @ip) + 1,LEN(@ip)))) END ELSE BEGIN SET @op = @ip + ' ' + @op SET @ip = '' END END RETURN @op END -- Usage SELECT [dbo].[fn_ReverseWordsInSentence] ('My Name is Pinal Dave') /* Dave Pinal is Name My */
Let me know if you have a better way to solve this problem.
Reference: Pinal Dave (https://blog.sqlauthority.com)