0

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+$"

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Yoann Pageaud
  • 412
  • 5
  • 22
  • That is a horrible table design. You should really consider redesigning the whole thing. – juergen d Jan 16 '18 at 10:04
  • It is maybe horrible but it is a valid way to do it, check this post: https://stackoverflow.com/questions/3694276/what-are-valid-table-names-in-sqlite If you have a better idea feel free to post it : in principle table contain data coming from 2 different series of numbers. So I simply wrote table names <1stserie>_<2ndserie>. How would you have done this? Thank you. – Yoann Pageaud Jan 16 '18 at 10:12

1 Answers1

1

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 '#';
CL.
  • 173,858
  • 17
  • 217
  • 259