Search code examples
regexsqlitesql-like

SQLite - display table names finishing by "_1"

I am looking for a way to display table names I have in a database for which the name is ending by "_1".

I tried to use the command:

.tables '%_1';

Unfortunately the underscore symbol is used in the expression matching, so it returned me tables such as: "125_1","125_11","125_21".
Only the first one is interesting in this example, I will not display the full result because there are hundreds of tables. So I tried something like this:

.tables '%_1' ESCAPE '_';

And it gave me the exact same result. If you have a solution to overcome the problem of the underscore symbol, please post it. remember that I have hundreds of tables with names following this pattern in regex: "^\d+_\d+$"

Solution

  • This is not how the ESCAPE clause works. To search for an underscore, you must escape the underscore with the escape character:

    LIKE '%#_1' ESCAPE '#'

    Anyway, .tables is not an SQL command and ignores the ESCAPE clause. To do your own search, you have to run your own query:

    SELECT name
FROM sqlite_master
WHERE type = 'table'
  AND name LIKE '%#_1' ESCAPE '#';