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)
9 Comments. Leave new
For sure with CLR function would be faster
DECLARE @Input nvarchar(max)
SET @Input = ‘I am Sanjay Monpara’
DECLARE @Output nvarchar(max)
SET @Output = ”
SELECT @Output = strings + ‘ ‘ + @Output FROM dbo.split(@Input,’ ‘)
print @Output
–>———————————————————————————————–
Here I have used my routine table valued function split (normally it should have in utility collection of all dba/developer)
create FUNCTION [dbo].[Split] (@string AS VARCHAR(8000), @splitAt AS VARCHAR(8000))
RETURNS @strings TABLE (Strings VARCHAR(8000))
AS
BEGIN
DECLARE @splitLen AS INT
DECLARE @index AS INT
SET @splitLen = LEN(@splitAt)
SET @splitAt = ‘%’ + @splitAt + ‘%’
SET @index = PATINDEX(@splitAt, @string)
WHILE(@index > 0)
BEGIN
INSERT INTO @strings VALUES(SUBSTRING(@string, 1, @index-1))
SET @string = SUBSTRING(@string, @index + 1, LEN(@string))
SET @index = PATINDEX(@splitAt, @string)
END
IF LEN(@string) > 0
INSERT INTO @strings VALUES(@string)
RETURN
END
Sanjay, I think your solution might return incorrect result due to the order of the returned string pieces is not guaranteed there.
Am I right?
Thank you Yuri for reply,
Yuri, this function works FIFO base so I think not an issue, but If any case in your mind please share it, I welcome it.
Maybe it’s just my paranoia :) but I used to trust only ORDER BY clause when I work with tables (read unsorted sets). Your function returns a table that’s why I made a remark.
Btw, is FIFO base a some guaranteed principle here in T-SQL table-valued functions or …? I ask because I’ve never heard/read about it…
My version (I like set-based solutions most of all :))
/* This auxiliary table may be useful in the future, you know…
create table Nums (num int primary key);
with
cteNums8000
as (
select top (8000) ROW_NUMBER () OVER (ORDER BY (SELECT 0)) as [num]
from sys.columns a
cross join sys.columns b
)
insert into Nums
select num from cteNums8000;
go
*/
declare @ip varchar (8000) = ‘My Name is Pinal Dave’;
with
cteSplit
as (
select SUBSTRING (@ip, num, CHARINDEX (‘ ‘, @ip + ‘ ‘, num) – num) as [value]
, num
from Nums
where num <= LEN (@ip)
and SUBSTRING (' ' + @ip, num, 1) = ' '
)
select STUFF ((select ' ' + value
from cteSplit
order by num desc
for xml path (''), type).value ('.', 'varchar (8000)')
, 1, 1, '')
Performance of original solution is better than solution with CTE.
Following solution is same as original, but little shorter.
DECLARE @ip NVARCHAR(MAX)
SET @ip = ‘My Name is Pinal Dave’
DECLARE @op NVARCHAR(MAX)
SET @op = ”
SET @ip=@ip+ ‘ ‘
WHILE CHARINDEX(‘ ‘, @ip) > 0
BEGIN
SELECT @op = SUBSTRING(@ip,0,CHARINDEX(‘ ‘, @ip)) + ‘ ‘+ @op
, @ip= LTRIM(SUBSTRING(@ip,CHARINDEX(‘ ‘, @ip) + 1,LEN(@ip)))
END
SELECT @op
Here’s my solution to this. Essentially slice the string up by the passed in delimiter, in this case a space, then reconstruct the string in reverse order.
declare @String nvarchar(50)
declare @Delimiter char(1)
declare @count int
declare @slice varchar(8000)
declare @rtnString nvarchar(50)
set @Delimiter = ‘ ‘
set @String = ‘I am Pinal Dave’
set @rtnString = ”
set @count = 1
while @count 0
begin
set @count = charindex(@Delimiter,@String)
if @count 0
set @slice = left(@String,@count – 1)
else
set @slice = @String
if(len(@slice) > 0)
set @rtnString = @slice + ‘ ‘ + @rtnString
set @String = right(@String,len(@String) – @count)
end
select @rtnString
Hello, I wonder if this piece of code could work for reversing a string
declare @name varchar(50)=’My name is Krittika Naik’;
declare @reversedname varchar(50)=”;
declare @storage varchar(50);
declare @i int =LEN(@name);
declare @j int =1;
while(@j<=LEN(@name))
begin
select @storage=SUBSTRING(@name,@i,1)
select @reversedname=@reversedname+@storage;
select @i=@i-1;
select @j=@j+1;
end
select @reversedname