How to Print the Starting and End Position of Each Word Using CTE?

The Common Table Expressions (CTE) were introduced into standard SQL in order to simplify various classes of SQL Queries for which a derived table was just unsuitable. In this article, we will see, that in a given string, which contains alphabets and asterisks, print the starting and end position of each word.

Steps To Print the Starting and End Position of Each Word Using CTE

Step 1: Create a Database.

Query:

CREATE DATABASE GFG

Step 2: Use the Database.

Query:

USE GFG

Step 3: Declare a variable of type NVARCHAR and assign the string. We will use STRING_SPLIT() method to split the string with delimiter as asterisk and calculate the start and end position of each word.

Query:

DECLARE @Str NVARCHAR(30)='Abc***Ghi*KLM****RST';

WITH CTE(Word,StartPosition,EndPosition) 
AS(
 SELECT 
   VALUE, 
   CHARINDEX(VALUE,@Str)-1,
   LEN(VALUE)+CHARINDEX(VALUE,@Str)-2 
 FROM STRING_SPLIT(@Str,'*') 
 WHERE VALUE!=' '
)
SELECT * FROM CTE

Output:

FAQs on Print the Starting and End Position of Each Word Using CTE

Q.1: Why do we need CTE?

Answer:

CTEs, like database views and derived tables, can make complex queries easier to develop and manage by making them more legible and straightforward. By disassembling complicated searches into manageable chunks that may be reused when rebuilding the query, we can achieve this property.

Q.2: Explain some Advantages of CTE.

Answer:

• Code maintenance is made simpler with CTE.
• The readability of the code is improved via CTE.
• It improves the query’s performance.

Q.3: How can we use 2 CTE in one query?

Answer:

We can reference multiple CTEs in a single query by using a WITH operator.

Q.4: What is the limit of CTE in SQL?

Answer:

Recursive CTEs have the potential to run endlessly, so SQL Server provides safeguards that restrict the number of times the recursive query can be executed. The default Recursion Limit for SQL Server is set to 100, or 100 recursive query calls. The OPTION clause can be used to change this.