Reverse String Word by Word Using SQL

How to reverse string word by word in SQL SERVER

Altered your query a little, try this.

 declare @I varchar(20)
 declare @O varchar(20)
 declare @T varchar(20)
 set @I='My Name is sachin'
 set @O=''
 while CHARINDEX(' ',@I)>0
 begin
     set @T=left(reverse(@i),charindex(' ',reverse(@i))-1)
     set @O=@O+' '+REVERSE(@T)
     set @I=left(@i,len(@i)-(1+len(@t)))
     If CHARINDEX(' ',@I)=0
        set @O=@O+' '+@i
     select @o
 end

'If' block is added to append the last word, as the 'Empty Space' will not be available to append the last word in the sentence.

How to reverse string in column a word by word?

You need to split the stored strings using a splitter, that returns the substrings and the position of each substring. After that you can easily build the desired output.

If you use SQL Server 2017+, you may try a JSON-based approach. You need to transform each string into a valid JSON array (for example P1_P10_P45_P20 into ["'P1","P10","P45","P20"]), parse this array as a table with OPENJSON() and join the rows with STRING_AGG() to generate the expected output:

Table:

CREATE TABLE Data (Relation varchar(50))
INSERT INTO Data (Relation)
VALUES
   ('P1_P10_P45_P20'),
   ('P1_P14_P5_P22'),
   ('P1_P3'),
   ('P3_P4_P5_P2_P100_P2_P1')

Statement:

SELECT c.Relation
FROM Data d
OUTER APPLY (
   SELECT STRING_AGG([value], '_') WITHIN GROUP (ORDER BY CONVERT(int, [key]) DESC)
   FROM OPENJSON(CONCAT('["', REPLACE(d.Relation, '_', '","'), '"]'))
) c (Relation)

Result:

Relation
----------------------
P20_P45_P10_P1
P22_P5_P14_P1
P3_P1
P1_P2_P100_P2_P5_P4_P3

Reverse String Word by Word using SQL

Create a Function:

REGEXP_SUBSTR('Your text here','[^ ]+', 1, ?) will extract a word from the text using Space as a delimiter. Tt returns the original String itself on Exception!

CREATE OR REPLACE FUNCTION reverse_words (v_STRING IN VARCHAR2)
RETURN VARCHAR2
IS
   L_TEMP_TEXT  VARCHAR2(4000);
   L_FINAL_TEXT  VARCHAR2(4000);
   V_LOOPCOUNT NUMBER :=0;
   T_WORD VARCHAR2(4000);
BEGIN
      L_TEMP_TEXT := regexp_replace(V_STRING,'[[:space:]]+',' '); -- Replace multiple spaces as single
      LOOP
        v_LOOPCOUNT := v_LOOPCOUNT+1;
        T_WORD      := REGEXP_SUBSTR(L_TEMP_TEXT,'[^ ]+', 1, V_LOOPCOUNT);
        L_final_TEXT := T_WORD||' '||L_final_TEXT;
      EXIT WHEN T_WORD IS NULL;
      END LOOP;
   RETURN(TRIM(L_final_TEXT));
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(sqlerrm||chr(10)||dbms_utility.format_error_backtrace);
        RETURN V_STRING;
END reverse_words;
/

Sample Result:

You can call reverse_words(yourcolumn) from your_table

SQL> select reverse_words('Hello World! I Love StackOverflow') "Reversed" from dual;

Reversed
--------------------------------------------------------------------------------
StackOverflow Love I World! Hello

Reverse the order of words in T-SQL

You can create one small function in SQL to reverse a string like below:

DECLARE @source VARCHAR(MAX)
DECLARE @dest VARCHAR(MAX)
DECLARE @lenght INT

SET @source = 'We want to tell you we all love StackOverflow'
SET @dest = ''

WHILE LEN(@source) > 0
BEGIN
    IF CHARINDEX(' ', @source) > 0
    BEGIN
        SET @dest = SUBSTRING(@source,0,CHARINDEX(' ', @source)) + ' ' + @dest
        SET @source = LTRIM(RTRIM(SUBSTRING(@source,CHARINDEX(' ', @source)+1,LEN(@source))))
    END
    ELSE
    BEGIN
        SET @dest = @source + ' ' + @dest
        SET @source = ''
    END
END
SELECT @dest

SQL - reverse like. Is it possible?

Although I'd give Tomalak's comment some consideration, here's a solution that might fit your needs:

SELECT COUNT(*) FROM curse_words
WHERE "somesh*ttyperson" LIKE CONCAT('%', curse_word, '%');

In this way you are actually composing a LIKE comparison term for each of the curse words by prepending and appending a % (e.g. %sh*t%).

LIKE might be a bit expensive to query if you plan on having millions of curse words but I think it's reasonable to assume you aren't.

All you have to do now is test for this result being strictly equal to 0 to let the nickname through, or forbid it otherwise.

T-SQL: How to reverse values in a string

How about this:

select col Before,
  case 
    when col like '%,%' then col 
    when len(replace(col, ' ', '')) = len(col) -1 
      then reverse(substring(reverse(col), 1, charindex(' ', reverse(col))-1))+', '+substring(col, 1, charindex(' ', col)-1)
    else col
  end  After
from yourtable

See SQL Fiddle with Demo. The result is:

|       BEFORE |        AFTER |
-------------------------------
|     Test One |    One, Test |
| Test Two One | Test Two One |
|  Test, Three |  Test, Three |

---

Related Topics