Tag : cte


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

Common Table Expressions

What is CTE (Common Table Expressions)

A common table expression (CTE) is a temporary result set accessible within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

CTE does not hold any physical space in database like a derived table. But Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

We can also create Nested CTE to staging our data throughout multiple CTE’s

Below are the scenarios where a CTE can best fit:

  1. Recursive Queries Using Common Table Expressions.
  2. Instead of creating a view for only one query we can choose CTE. As like view it will not take create it’s definition inside metadata.
  3. Making queries readable (For complex and long queries)

Advantages : –

Use of CTE offers improved readability and ease in maintenance of complex queries. The query can be divided into blocks … More