How can I query "string ends with 'string'" SQLite

I have a db that has a filename column. I want my user to be able to choose an extension, and pull all the filenames that have that extension.

There have been threads on how to do this and I've followed a few different example but I don't know why my query doesn't work.

    private void button1_Click(object sender, EventArgs e)
    {
        string query = "SELECT * FROM " + tableName + " WHERE ";

        //string ext = "'%*" + comboBox1.Text + "%'";

        query += "filename LIKE '*.rtf';";

        queryDB(query);

    }

comboBox1 is where the selection occurs, however I stopped trying to make this work until I can figure out why the hardcoded "filename LIKE .rtf" does not work.

How can I check the file ends with .rtf? What am I doing wrong?

Thanks!!

edit: I know my queryDB function works, I can run "SELECT * from tablename" through and it works fine. It also might be worth noting that I display my db on screen. It defaults to show them all, but you can trim your results. When I try to run this query, all entries appear in the datagrid.

Solution

The correct way to structure this particular query is to use this syntax:

LIKE '%.rtf'

You should not use * in the string to match since this is an operating system wildcard and not a SQL wildcard, which means that unless you have an actual file named *.rtf (ie. the name really contains that asterix) then it won't match.

However, this is a poor solution because a match on "ends with string" cannot use an index. It will always end up doing a full table scan. This is bad.

The correct way to do this in the database is to do the following:

Add an extra column to your table, call it "extension" or some such
Add an index to this column
When inserting or updating rows, extract the file extensions with your programming language (it usually has routines to do exactly that so that you don't have to go hunting for dots) and place it into this new column.
Query on this column instead of using LIKE on the filename, then you can simply use this criteria:
```
WHERE extension = '.dbf'
```

This will typically be much faster in the long term.