Recursive CTE (Common Table Expression)

Recursive CTE is a more elegant solution for splitting delimited strings recursively. It involves recursively breaking down the string until all individual items are extracted. Although efficient, it might not be supported in all SQL environments and can be resource-intensive for large datasets.

Example: Extracting Color from ProductDescription using Recursive CTE in SQL

-- Create the ProductDetails table
CREATE TABLE ProductDetails (
    ProductID INT AUTO_INCREMENT PRIMARY KEY,
    ProductDescription VARCHAR(100)
);

-- Insert some sample data
INSERT INTO ProductDetails (ProductDescription) VALUES
('Shirt,Blue,Large'),
('Pants,Black,Medium'),
('Dress,Red,Small');

-- Query to extract the color from the ProductDescription column using Recursive CTE
WITH RECURSIVE ProductCTE AS (
    SELECT 
        ProductID,
        ProductDescription,
        SUBSTRING_INDEX(ProductDescription, ',', 1) AS ProductName,
        SUBSTRING_INDEX(SUBSTRING_INDEX(ProductDescription, ',', 2), ',', -1) AS Color,
        SUBSTRING_INDEX(ProductDescription, ',', -1) AS Size,
        1 AS StartIndex
    FROM 
        ProductDetails
    UNION ALL
    SELECT 
        ProductID,
        ProductDescription,
        SUBSTRING_INDEX(SUBSTRING_INDEX(ProductDescription, ',', StartIndex + 1), ',', -1) AS ProductName,
        SUBSTRING_INDEX(SUBSTRING_INDEX(ProductDescription, ',', StartIndex + 2), ',', -1) AS Color,
        SUBSTRING_INDEX(SUBSTRING_INDEX(ProductDescription, ',', StartIndex + 3), ',', -1) AS Size,
        StartIndex + 1 AS StartIndex
    FROM 
        ProductCTE
    WHERE 
        StartIndex < LENGTH(ProductDescription) - LENGTH(REPLACE(ProductDescription, ',', '')) + 1
)
SELECT 
    ProductDescription,
    Color
FROM 
    ProductCTE;

Output:

• Create a table named ProductDetails with a ProductDescription column to store the product details.
• Sample data is inserted into the ProductDetails table.
• We use a Recursive CTE(Common Table Expression) to split the ProductDescription column into its components (name, color, size).
• The Recursive CTE splits the string recursively based on the comma delimiter.

In SQL, dealing with delimited strings is a common task, especially when handling data that are not structured in a traditional tabular format. Whether it’s parsing a list of values separated by commas or any other delimiter, splitting these strings into individual items is crucial for various data manipulation tasks.

In SQL, sometimes we get data that’s all squished together, like a bunch of words separated by commas or other symbols. This article is all about learning how to do just that—take a long string of text and break it into pieces we can easily work with.