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]
DECLARE @op VARCHAR(MAX)
SET @op = ''
DECLARE @Lenght INT
WHILE LEN(@ip) > 0
IF CHARINDEX(' ', @ip) > 0
SET @op = SUBSTRING(@ip,0,CHARINDEX(' ', @ip)) + ' ' + @op
SET @ip = LTRIM(RTRIM(SUBSTRING(@ip,CHARINDEX(' ', @ip) + 1,LEN(@ip))))
SET @op = @ip + ' ' + @op
SET @ip = ''
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 (http://blog.sqlauthority.com)