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”
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
In this demo, i have used a common table expression with recursion. A recursive CTE consists of three parts;
- Anchor part – Part of CTE above UNION ALL
- Recursive part – Part below UNION ALL which will be called recursively
- 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.
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”.