RE: Question with wildcarded expressions for LIKE clause

30 Jul 2019

      Jenny,

Now that I have confirmed the syntax, I believe there might be a problem with like and not like as used below.

Here is the test schema:

set schema foof;
create table config (a integer);
create table data_0000 (a integer);
create table data_0001 (a integer);
create table data_0002 (a integer);
create table data_0003 (a integer);
create merge table data (a integer);
create table test_table (a integer);
create table main__yyyy_mm_dd__hh__0000 (a integer);
create table main__yyyy_mm_dd__hh__0001 (a integer);
create table main__yyyy_mm_dd__hh__0002 (a integer);
create table main__yyyy_mm_dd__hh__0003 (a integer);
create merge table main__yyyy_mm_dd__hh (a integer);
set schema sys;
select * from schemas where name = 'foof';

+--------------+------+----------------------+----------+-----------+
| id               | name | authorization | owner | system |
+========+======+===========+=======+=======+
| 1050825 | foof     |                      3 |           3 | false     |
+-------------+----------+------------------+-----------+-----------+

Select * from tables where schema_id = 1050825;

Shows all the 12 tables I created above

Select * from tables where schema_id = 1050825 and name like '%\_\_____';

Lists all 11 tables.  My belief is this should only list the 4 main__ tables and not the main__ merge table.  The only table not listed here is 'data'.

Doing the same query without the back-slashes gives the same result.

Doing the complement (not like, instead of like), gives the data table.

For the like expression, I would have expected only 4 tables.  For the not like, I would have expected 8 tables.

Is this a bug?  Confirming from your response, \ escapes _ to change it from any single character to only the character _?

Dave

-----Original Message-----
From: users-list  On Behalf Of Ying Zhang
Sent: Tuesday, July 30, 2019 4:55 AM
To: Communication channel for MonetDB users 
Subject: Re: Question with wildcarded expressions for LIKE clause

This message originated outside of NETSCOUT. Do not click links or open attachments unless you recognize the sender and know the content is safe.
...
On 25 Jul 2019, at 02:32, Gotwisner, Dave  wrote:
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.
Hai Dave, your understanding of the definitions of the wild card characters is correct.

Jennie
...
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
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://urldefense.proofpoint.com/v2/url?u=https-3A__www.monetdb.org_mailman_listinfo_users-2Dlist&d=DwIGaQ&c=Hlvprqonr5LuCN9TN65xNw&r=u41iOHYIJhkr4P2f2CDLh8D6LDUMXy2xJPhXyGKDzns&m=qKnnHkmN-t1tWFOLvPwsqL3NDe40joYp2MqEu57Lbho&s=jEq0840pRXkiOuawO9EM6YB00D2d3iUplyunk5iQRjY&e=
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://urldefense.proofpoint.com/v2/url?u=https-3A__www.monetdb.org_mailman_listinfo_users-2Dlist&d=DwIGaQ&c=Hlvprqonr5LuCN9TN65xNw&r=u41iOHYIJhkr4P2f2CDLh8D6LDUMXy2xJPhXyGKDzns&m=qKnnHkmN-t1tWFOLvPwsqL3NDe40joYp2MqEu57Lbho&s=jEq0840pRXkiOuawO9EM6YB00D2d3iUplyunk5iQRjY&e=