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.

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.

SQLite does not have a built-in function for this purpose, so we will need to use custom functions or queries to achieve the desired result. Below is the method which helps us to split delimited strings are as follows:

1. Using the SUBSTR() and INSTR() Functions
   
2. Using the Recursive Common Table Expression (CTE)
   
3. Creating a Custom Split Function

To understand how to split a delimited string to access individual items in SQLite we need a table on which we will perform various operations and queries. Here we will consider a table called Product which contains ID, name as Columns.

CREATE TABLE Product (
    id INTEGER PRIMARY KEY,
    names TEXT
);
INSERT INTO Product (names) VALUES ('apple,banana'), ('mango,orange');

After inserting some records into the Product table, The table looks:

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.

Consider a scenario where we have a table with a column containing delimited strings:

SELECT
    id,
    SUBSTR(names, 1, INSTR(names, ',') - 1) AS item1,
    SUBSTR(names, INSTR(names, ',') + 1) AS item2
FROM
    Product;

Output:

Explanation: In the above query, we extracts two items from the 'names' column in the 'Product' table and splitting them based on the comma (',') delimiter. It uses the SUBSTR function to extract the first item from the start of the string to the first comma, and the second item from after the first comma to the end of the string.

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,
    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.

3. Creating a Custom Split Function

import sqlite3

def 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 database
conn = sqlite3.connect(":memory:")

# Create the Product table
conn.execute("CREATE TABLE Product (id INTEGER PRIMARY KEY, names TEXT)")

# Insert sample data
conn.execute("INSERT INTO Product (names) VALUES ('apple,banana'), ('mango,orange')")

# Create the custom split function
create_split_function(conn)

# Usage example
cursor = conn.cursor()
cursor.execute("SELECT id, split_string(names, ',') FROM Product")
print(cursor.fetchall())

# Close the connection
conn.close()

Output:

Explanation: In the above query we use Python code demonstrates how to create a custom SQLite function to split a delimited string. It creates a split_string function that splits a string into a list based on a given delimiter. This function is then registered with the SQLite connection using create_function. Finally, the function is used in a SQL query to split the names column in the Product table and retrieve the result.

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.