Reverse the string with a delimiter (Recursive CTE) 1


In this article, we are going to learn how we can “Reverse string in sql server” with some specified delimiter e.g, based on space or comma or tilde or semi-colon etc;

If you will input “A B C”, the output should be as “C B A”

Solution:

Lets assume space as a delimiter in this example and now to achieve our goal, we will use a recursive CTE(Common table expression). Just have a look on below code and then we will discuss in detail;


DECLARE @VAR VARCHAR(100)

SET @VAR = 'GOPAL KRISHNA RANJAN'

DECLARE @StringToReturn VARCHAR(1000)

SET @StringToReturn= ''

;

WITH CTE AS

(

SELECT CAST(LEFT(@VAR,CHARINDEX(' ', @VAR + ' ')-1) AS VARCHAR(100)) VARS, STUFF(@VAR,1,CHARINDEX(' ', @VAR + ' '),'') VAR1

,ROW_NUMBER() OVER(ORDER BY @VAR) SEQ

UNION ALL

SELECT CAST(LEFT(VAR1,CHARINDEX(' ',VAR1 + ' ')-1) AS VARCHAR(100)), STUFF(VAR1,1,CHARINDEX(' ',VAR1 + ' '),'') VAR2,SEQ + 1 FROM CTE

WHERE VAR1 > ''

)

SELECT @StringToReturn = @StringToReturn + COALESCE(VARS + ' ', '', '') FROM CTE ORDER BY SEQ DESC

SELECT @StringToReturn

Detail:-

In this demo, i have used a common table expression with recursion. A recursive CTE consists of three parts;

  1. Anchor part – Part of CTE above UNION ALL
  2. Recursive part – Part below UNION ALL which will be called recursively
  3. Condition to break – Condition to break the recursion where certain condition met

In sample code, i have used a trick that in anchor tag of CTE i have created three columns. In column 1, i extracted the first string up  to delimiter and then in column 2 i have stuffed that part of string from entire string and in column 3 , i just added a serial number.

In recursive part of the CTE, I have repeated this task until the entire string is stuffed.

Conclusion

In recursive CTE, we can implement such type of things, which is better than using a loop to achieve the same goal. In this post I have shared the recursive way of doing this but we can also achieve this using CLR or some Join tricks. Thanks for reading “Reverse string in sql server”.

Rate This
[Total: 0    Average: 0/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

One thought on “Reverse the string with a delimiter (Recursive CTE)

  • Sartu

    I enjoyed your Set Based Videos but not sure the best approach for matching the column 1(ACCT) to the Match column. If you will note the value C will match A and A will match B and B will match C etc but they all are related to C. Ideally I should be able to consolodate the Acct to 2 distinct minimum UID’s with the rest being eliminated.

    ACCT Match UID
    a b 1
    a c 2
    b a 3
    b c 4
    c a 5
    c b 6
    c d 7
    d c 8
    e f 9
    f e 10