Modifying SQLite Search Parameters to Return Full String From One Word Instead of Specific Order

Question

I am having trouble with altering the search parameters within SQLite, currently, the search functions that I have for searching film titles and genres can only return the specific result, e.g., Action will only bring back Action films and not Action, Adventure films.

I have had a friend manage to have it work within the SQLite Studio itself, but using this method has been unsuccessful. He suggested using the following ORDER BY, however, it still gives the same results.

 Cursor c = sqdb.rawQuery("SELECT * FROM filmography WHERE genre LIKE '%" + searchGenre + "' ORDER BY LENGTH(genre) ASC",
                null);

I am at a loss now as I cannot seem to come across or figure out the answer. How would I go about changing the SQL so that if you were to search say "Action" in the genre search, it would bring back all results so eg., "Film 1 = Action, Film 2 = Action, Adventure, Film 3 = Action, Crime" and so on?

Example of genre data: "Capone, Biography, Crime, Drama, Fonse, 2020, 4.7/10 Black Hawk Down, Drama, History, War, Twombly, 2001, 7.7/10 Venom, Action, Adventure, Sci-Fi, Eddie Brock / Venom, 2018, 6.7/10

 public String searchByTitleInFilmography(SQLiteDatabase sqdb, String searchfilmTitle)
    {
        String result = "";

        Cursor c = sqdb.rawQuery("SELECT * FROM filmography WHERE filmTitle =  '" + searchfilmTitle + "'",
                null);

        if (c != null)
        {
            if (c.moveToFirst())
            {
                do
                {
                    String id = c.getString(0);
                    result = result + id + ": ";

                    String filmtitle = c.getString(1);
                    result = result + "Title: " + filmtitle + ". ";

                    String genre = c.getString(2);
                    result = result + "Genre(s): " + genre + ". ";

                    String role = c.getString(3);
                    result = result + "Role: " + role + ". ";

                    String year = c.getString(4);
                    result = result + "Released: " + year + ". ";

                    String imdbrating = c.getString(5);
                    result = result + "Rating: " + imdbrating + "\n" + "\n";

                    Log.w("FILM_TITLE_GENRE", "ID - " + id +":" + " Genre(s) = " + genre);

                } while (c.moveToNext());
            }
            else
            {
                result = "No Films Found With The Title = " + searchfilmTitle;
            }
        }
        c.close();

        return result;

    } //  public String serachByTitleInFilmography(SQLiteDatabase sqdb, String searchTitle)

Answer 1

If the column genre contains values like a comma separated list of genres, then you should change the operand of the operator LIKE like this:

String sql = "SELECT * FROM filmography WHERE ',' || REPLACE(genre, ', ', ',') || ',' LIKE '%,' || ? || ',%'"
Cursor c = sqdb.rawQuery(sql, new String[] {searchGenre});

REPLACE() removes all spaces after each comma and each value of genre becomes like: ',Action,Crime,Drama,'.
When you pass 'Action' as the parameter for the ? placeholder then the sql statement becomes:

SELECT * FROM filmography WHERE ',Action,Crime,Drama,' LIKE '%,Action,%'

which is what you want: all the rows that contain 'Action' anywhere in the column genre.