Daily Archives: Sep 23, 2014


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 > 
More