How to use wildcards to match directory paths in Sqlite

Question

I have a db containing folder paths and I'd like to find all folders contained by some folder. I get partial results with:

select * from pathTable where Path like ?||'%'

Given

c:\\root\\1
c:\\root\\1 Copy
c:\\root\\1\\2
c:\\root\\1\\3\\3a
c:\\root\\1\\4

When ? is "c:\\root\\1", the above query returns

c:\\root\\1
c:\\root\\1 Copy

I would like to get all subfolders too. I suspect the Sqlite is stumbling on the '\' in the stored paths. Anyone know what I've done wrong?

Answer 1

Here is what I had to do to allow the string parameter to be "fully" escaped via the @ symbol.

void printPaths()
    {
        string mypath = @"c:\\root\\1";
        string sql = ("select * from paths where pathdesc like @mypath");

        SQLiteCommand command = new SQLiteCommand(sql,m_dbConnection);
        command.Parameters.AddWithValue("@mypath", mypath+"%");

        SQLiteDataReader reader = command.ExecuteReader();
        while (reader.Read())
            Console.WriteLine("ID: " + reader["pathid"] + "\tpathdesc: " + reader["pathdesc"]);
        Console.ReadLine();
    }