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;

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