Split a SQLite row bij given delimiter

Question

I'm written a "movie" program in VB.NET

Users will be able to create custom filters where they can specify which movies they want to see in a list based on some simple things like genre, director, release year etc.

Table movie's:

ID     Title     Director     Year     Genres      Description     Image

Since a movie (or tv show) can have more genres I ended up with storing the the genres into 1 string with a ; as delimiter.

Example:

a01;a02;b08;c01

Of course my application converts a01 to the appropriate "Horror" or "Thriller" genre etc. etc. on retrieving the results.

My question is: is it possible to create a SQLite query that does the filtering itself? At the moment I'm creating queries like:

SELECT * 
FROM `movies` 
WHERE (`Year` = 2013) 
  AND (`Genres` LIKE '%a02%' OR `Genres` LIKE '%g05%')

This does the trick, however based on some custom filters, you can end up with queries over 10kb of chars.

Is there some sort of SPLIT function in SQLite so that I could query like this:

SELECT * 
FROM `movies` 
WHERE (`Year` = '2013') 
  AND (SPLIT(`Genres`,';') IN ('a01','v05','c01'))

Any help would be very appreciated

Answer 1

[Sorry for the T-SQL, but MS SQL Server is all I have to demonstrate with. The SQL should be very similar if not the same for SQLite.]

What you should really do is create another table for the genres:

Then you query it with something like

SELECT DISTINCT M.[ID] AS [ID], [Title] AS [Title] FROM Movies AS M
JOIN [MovieGenre] AS MG
ON M.ID = MG.ID
WHERE (MG.Genre IN ('A01', 'A02'))
AND M.[Year] = 2010

From what I can tell with a quick look, it is a bit fiddly to split strings into a table in SQLite How to split comma-separated value in SQLite? - note the comments.

However, if you can, then you can use something like

-- This is a temporary table in SQL Server.
-- It is only to illustrate joining onto a table of
-- user-selected genres
CREATE TABLE #genres
(
    Genre NCHAR(10)
)
INSERT INTO #genres VALUES ('A01');
INSERT INTO #genres VALUES ('B01');

-- The query:
SELECT DISTINCT M.[ID] AS [ID], [Title] AS [Title] FROM Movies AS M
JOIN [MovieGenre] AS MG
ON M.ID = MG.ID
JOIN #genres AS TempG
ON TempG.Genre = MG.Genre
AND M.[Year] = 2010

where the #genres table is the table you get from splitting the string.

However as you are creating the queries in VB.NET, you can split the string easily in that and use it to build an IN clause as a string easily enough - a small code sample to illustrate that:

Module Module1

    Sub Main()
        Dim userSelectedGenres = "A01;A02"
        Dim gs = userSelectedGenres.Split(";"c)
        Dim inClause = String.Join(",", gs.Select(Function(g) "'" & g & "'"))

        Dim sqlString = <sql>SELECT DISTINCT M.[ID] AS [ID], [Title] AS [Title] FROM Movies AS M
JOIN [MovieGenre] AS MG
ON M.ID = MG.ID
WHERE (MG.Genre IN (<%= inClause %>))
AND M.[Year] = 2010
</sql>.Value

        Console.WriteLine(sqlString)

        Console.ReadLine()

    End Sub

End Module

Outputs:

SELECT DISTINCT M.[ID] AS [ID], [Title] AS [Title] FROM Movies AS M
JOIN [MovieGenre] AS MG
ON M.ID = MG.ID
WHERE (MG.Genre IN ('A01','A02'))
AND M.[Year] = 2010