# 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]

### About Gopal Krishna Ranjan

Gopal is a passionate Data Engineer and Data Analyst. He has implemented many end to end solutions using Big Data, Machine Learning, OLAP, OLTP, and cloud technologies. He loves to share his experience at https://www.sqlrelease.com/. Connect with Gopal on LinkedIn at https://www.linkedin.com/in/ergkranjan/.

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