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.
How to Split a Delimited String to Access Individual Items in SQL?
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.