SQL SERVER – Reverse String Word By Word

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)

, ,
Previous Post
SQL SERVER – FIX: Error 5161 – An Unexpected file id was Encountered
Next Post
SQL SERVER – Finding if Current Week is Odd or Even – Script

Related Posts

9 Comments. Leave new

  • For sure with CLR function would be faster

    Reply
  • Sanjay Monpara
    December 8, 2014 7:32 pm

    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

    Reply
    • 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?

      Reply
      • 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, '')

    Reply
    • 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

      Reply
  • Paul (@__PaulG)
    December 16, 2014 8:19 pm

    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

    Reply
  • 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

    Reply

Leave a Reply

Menu