Reverse the string with a delimiter (Recursive CTE)

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”.