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

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.


Leave a comment

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

*

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