I am trying to do some constraints off the system tables to select a unique pattern of tables.
We have tables like the following (I can't give the actual table names):
* Config (a single record exists here, no merge tables use it) * Data_0000 (a small number of records exist in each of these) * Data_0001 * Data_0002 * Data_0003 * Data (this is a merge table over the last 4) * Test_table (a small number of records exist here, no merge tables use it) * Main__yyyy_mm_dd__hh__0000 (millions of records, yyyy, mm, dd, hh are all numbers, we have one set of tables per hour, and keep significantly over 1 weeks' worth of data) * Main__yyyy_mm_dd__hh__0001 * Main__yyyy_mm_dd__hh__0002 * Main__yyyy_mm_dd__hh__0003 * Main__yyyy_mm_dd__hh (this is a merge table over the last 4)
If I do a select * from sys.tables where schema_id in (select id from schemas where name = 'frodo') and type in (select table_type_id from table_types where table_type_name = 'TABLE') I get the right thing - all non merge tables. I then want to further break it down into the tables that end in __[0-9][0-9][0-9][0-9] and those that don't (two separate queries for two separate purposes). Consider frodo the name of our schema.
Adding and name like 'data______' appears to work. This should give me the 4 data_* tables.
Adding and name like '%______' gives me all non-merge tables, including config and test_table. I'm assuming % is the wild card for 0 or more, and _ is the wild card for 1. This matches some documentation I have found. The \ are required because I need to explicitly look for 2 of the underscore character.
Am I doing this correctly? Are these the right wildcarding characters? MS SQLServer gives a # as a match for a single digit, is there an equivalent? Or, is this just a bug in MonetDB?
Thanks, Dave