Home SQL How to use the Recursive Common Table Expression (CTE) In SQL

How to use the Recursive Common Table Expression (CTE) In SQL

1. Using the SUBSTR() and INSTR() Functions

3. Creating a Custom Split Function 

WITH RECURSIVE Splitter AS (
    SELECT
        id,
        SUBSTR(names, 1, INSTR(names, ',') - 1) AS part,
        SUBSTR(names, INSTR(names, ',') + 1) AS remainder
    FROM
        Product
    UNION ALL
    SELECT
        id,
        SUBSTR(remainder, 1, INSTR(remainder, ',') - 1) AS part,
        SUBSTR(remainder, INSTR(remainder, ',') + 1) AS remainder
    FROM
        Splitter
    WHERE
        remainder != ''
)
SELECT
    id,
    part
FROM
    Splitter;

Output:

Explanation: In the above query, we does a recursive query splits the ‘names‘ column in the ‘Product‘ table into individual parts separated by commas. It uses the initial SELECT to split the first part and the remainder of the string. The UNION ALL combines this with subsequent SELECTs that continue splitting the remainder until no more commas are found. The final SELECT retrieves the ‘id‘ and ‘part‘ columns from the Splitter table, showing each part of the original ‘names’ column on a separate row.

How to Split a Delimited String to Access Individual Items in SQLite?

In SQLite, managing delimited strings can be a common requirement, especially when dealing with data that needs to be stored or manipulated in a database. Delimited strings are essentially strings where items are separated by a specific delimiter character, such as commas, pipes, or tabs. Splitting these strings into individual items can be important for various database operations and queries.

In this article, we will explore different methods to efficiently split delimited strings in SQLite to access individual items with the help of various methods along with the examples and so on.

Similar Reads

How to Split String in SQLite?

When working with SQLite databases, we may encounter situations where data is stored in a delimited format, such as comma–separated values. To access individual items within such strings, we need to split them into separate elements....

1. Using the SUBSTR() and INSTR() Functions

One approach to split delimited strings in SQLite involves using the SUBSTR() and INSTR() functions. These functions are available in SQLite and can be used to extract substrings based on character positions....

2. Using the Recursive Common Table Expression (CTE)

WITH RECURSIVE Splitter AS ( SELECT id, SUBSTR(names, 1, INSTR(names, ',') - 1) AS part, SUBSTR(names, INSTR(names, ',') + 1) AS remainder FROM Product UNION ALL SELECT id, SUBSTR(remainder, 1, INSTR(remainder, ',') - 1) AS part, SUBSTR(remainder, INSTR(remainder, ',') + 1) AS remainder FROM Splitter WHERE remainder != '')SELECT id, partFROM Splitter;...

3. Creating a Custom Split Function

import sqlite3def split_string(delimited_text, delimiter=','): return delimited_text.split(delimiter)def create_split_function(conn): conn.create_function("split_string", 1, split_string)# Connect to the SQLite databaseconn = sqlite3.connect(":memory:")# Create the Product tableconn.execute("CREATE TABLE Product (id INTEGER PRIMARY KEY, names TEXT)")# Insert sample dataconn.execute("INSERT INTO Product (names) VALUES ('apple,banana'), ('mango,orange')")# Create the custom split functioncreate_split_function(conn)# Usage examplecursor = conn.cursor()cursor.execute("SELECT id, split_string(names, ',') FROM Product")print(cursor.fetchall())# Close the connectionconn.close()...

Conclusion

Overall, In this article you have learned how to split a delimited string to access individual items in SQLite. You explored two different approaches such as using the SUBSTR() and INSTR() functions and creating a custom split function. By applying these techniques, you can efficiently work with delimited strings in SQLite and handle complex data structures with ease....

Tags:
#SQLite Query #Databases #SQLite

1. Using the SUBSTR() and INSTR() Functions

3. Creating a Custom Split Function

Contact Us

Categories
Learn the most popular programming languages in the world.