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