@sinhaa wrote:
@jauling_chou
Your query to get the result you want is not right. It should be:
AND aggregate.name RLIKE 'aggr[13]{1}[0-9]{1}.*'
As I said, WFA support any SQL feature that mysql supports. Its not different than any MYsql client.
I would suggest to connect to WFA DB using any MYSQL clients like workbench, SQL Yog, DB visualizer etc. and run your queries. This would easily allow you to test the validity of your queries keeping WFA GUI aside.
For WFA DB login credentials etc. , See the WFA documentations.
And yes SQL query AUTOcomplete doesn't include everything that Mysql has to offer. But they will work.
sinhaa
Actually, the {1} is entirely optional, given that [01] and [0-9] character classes already mean that they match one of the characters. Regardless, even if I try your version, it does not work. I've resorted to using the ginormous OR conditional instead.
I played around a bit more using the built-in mysql.exe in WFA, and finally figured it out. Its because RLIKE/REGEXP doesn't take % symbols! Once I switched over to using proper wildcard regexp, it works as intended. Thanks for the tip! FWIW, I completely at first overlooked the .* in your example.
C:\Program Files\NetApp\WFA\mysql\bin>mysql -u wfa -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 97110
Server version: 5.6.23-enterprise-commercial-advanced MySQL Enterprise Server -Advanced Edition (Commercial)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT aggregate.name FROM cm_storage.aggregate WHERE aggregate.name LIKE 'aggr3%';
+--------------------+
| name |
+--------------------+
| aggr31_1a |
| aggr31_1b |
| aggr31_4a_reserved |
| aggr3_1a |
+--------------------+
4 rows in set (0.00 sec)
mysql> SELECT aggregate.name FROM cm_storage.aggregate WHERE aggregate.name RLIKE 'aggr3%';
Empty set (0.00 sec)
mysql> SELECT aggregate.name FROM cm_storage.aggregate WHERE aggregate.name RLIKE 'aggr31_1a';
+-----------+
| name |
+-----------+
| aggr31_1a |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT aggregate.name FROM cm_storage.aggregate WHERE aggregate.name RLIKE 'aggr3[0-9]%';
Empty set (0.00 sec)
mysql> SELECT aggregate.name FROM cm_storage.aggregate WHERE aggregate.name RLIKE 'aggr3[0-9].*';
+--------------------+
| name |
+--------------------+
| aggr31_1a |
| aggr31_1b |
| aggr31_4a_reserved |
+--------------------+
3 rows in set (0.00 sec)
mysql> SELECT aggregate.name FROM cm_storage.aggregate WHERE aggregate.name RLIKE 'aggr[13][0-9].*';
+--------------------+
| name |
+--------------------+
| aggr10_1a |
| aggr10_1a |
| aggr10_1b |
| aggr10_1b |
| aggr10_1b |
| aggr10_1b |
| aggr10_1b |
| aggr10_1b |
| aggr10_1b |
| aggr10_2b |
| aggr10_2b_reserved |
| aggr10_5b |
| aggr11_1a |
| aggr11_1b |
| aggr11_1b |
| aggr11_2a |
| aggr11_2b |
| aggr11_2b |
| aggr12_2a |
| aggr12_2b |
| aggr15k_1b |
| aggr31_1a |
| aggr31_1b |
| aggr31_4a_reserved |
+--------------------+
24 rows in set (0.02 sec)
