You can reverse string word by word with a small user defined function. 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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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