| # Licensed to the Apache Software Foundation (ASF) under one |
| # or more contributor license agreements. See the NOTICE file |
| # distributed with this work for additional information |
| # regarding copyright ownership. The ASF licenses this file |
| # to you under the Apache License, Version 2.0 (the |
| # "License"); you may not use this file except in compliance |
| # with the License. You may obtain a copy of the License at |
| # |
| # http://www.apache.org/licenses/LICENSE-2.0 |
| # |
| # Unless required by applicable law or agreed to in writing, |
| # software distributed under the License is distributed on an |
| # "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| # KIND, either express or implied. See the License for the |
| # specific language governing permissions and limitations |
| # under the License. |
| |
| # Initialize tables |
| CREATE TABLE foo (name VARCHAR(24), |
| email VARCHAR(24)); |
| |
| INSERT INTO foo VALUES('aaa', 'aaa@gmail.com'); |
| INSERT INTO foo VALUES('bbb', 'BBB@outlook.com'); |
| INSERT INTO foo VALUES('ccc', 'ccc@gmail.com'); |
| INSERT INTO foo VALUES('ddd', 'delta@wisc.edu'); |
| INSERT INTO foo VALUES('eee', 'eee@wisc.edu'); |
| INSERT INTO foo VALUES('eef', 'fff@cs.wisc.edu'); |
| |
| CREATE TABLE bar (pattern VARCHAR(24), |
| organization VARCHAR(24)); |
| |
| INSERT INTO bar VALUES('%@gmail.com', 'Google'); |
| INSERT INTO bar VALUES('%@outlook.com', 'Microsoft'); |
| INSERT INTO bar VALUES('%@wisc.edu', 'UW Madison'); |
| INSERT INTO bar VALUES('%@cs.wisc.edu', 'UW Madison CS'); |
| |
| |
| SELECT * |
| FROM foo |
| WHERE email LIKE '%@gmail.com'; |
| -- |
| +------------------------+------------------------+ |
| |name |email | |
| +------------------------+------------------------+ |
| | aaa| aaa@gmail.com| |
| | ccc| ccc@gmail.com| |
| +------------------------+------------------------+ |
| == |
| |
| SELECT * |
| FROM foo |
| WHERE email NOT LIKE '%@gmail.com'; |
| -- |
| +------------------------+------------------------+ |
| |name |email | |
| +------------------------+------------------------+ |
| | bbb| BBB@outlook.com| |
| | ddd| delta@wisc.edu| |
| | eee| eee@wisc.edu| |
| | eef| fff@cs.wisc.edu| |
| +------------------------+------------------------+ |
| == |
| |
| SELECT * |
| FROM foo |
| WHERE name LIKE 'ee_'; |
| -- |
| +------------------------+------------------------+ |
| |name |email | |
| +------------------------+------------------------+ |
| | eee| eee@wisc.edu| |
| | eef| fff@cs.wisc.edu| |
| +------------------------+------------------------+ |
| == |
| |
| SELECT * |
| FROM foo |
| WHERE email REGEXP '.*(gmail|outlook).*'; |
| -- |
| +------------------------+------------------------+ |
| |name |email | |
| +------------------------+------------------------+ |
| | aaa| aaa@gmail.com| |
| | bbb| BBB@outlook.com| |
| | ccc| ccc@gmail.com| |
| +------------------------+------------------------+ |
| == |
| |
| SELECT * |
| FROM foo |
| WHERE email NOT REGEXP '.*(gmail|outlook).*'; |
| -- |
| +------------------------+------------------------+ |
| |name |email | |
| +------------------------+------------------------+ |
| | ddd| delta@wisc.edu| |
| | eee| eee@wisc.edu| |
| | eef| fff@cs.wisc.edu| |
| +------------------------+------------------------+ |
| == |
| |
| SELECT * |
| FROM foo |
| WHERE email REGEXP '[a-z]+\@.*'; |
| -- |
| +------------------------+------------------------+ |
| |name |email | |
| +------------------------+------------------------+ |
| | aaa| aaa@gmail.com| |
| | ccc| ccc@gmail.com| |
| | ddd| delta@wisc.edu| |
| | eee| eee@wisc.edu| |
| | eef| fff@cs.wisc.edu| |
| +------------------------+------------------------+ |
| == |
| |
| SELECT * |
| FROM foo |
| WHERE name REGEXP '.[ace].' |
| AND email LIKE '%wisc%'; |
| -- |
| +------------------------+------------------------+ |
| |name |email | |
| +------------------------+------------------------+ |
| | eee| eee@wisc.edu| |
| | eef| fff@cs.wisc.edu| |
| +------------------------+------------------------+ |
| == |
| |
| SELECT * |
| FROM foo |
| WHERE name REGEXP '.[ace].' |
| OR email LIKE '%wisc%'; |
| -- |
| +------------------------+------------------------+ |
| |name |email | |
| +------------------------+------------------------+ |
| | aaa| aaa@gmail.com| |
| | ccc| ccc@gmail.com| |
| | ddd| delta@wisc.edu| |
| | eee| eee@wisc.edu| |
| | eef| fff@cs.wisc.edu| |
| +------------------------+------------------------+ |
| == |
| |
| SELECT name, email, organization |
| FROM foo, bar |
| WHERE email LIKE pattern |
| AND name NOT LIKE 'd%' |
| ORDER BY name ASC; |
| -- |
| +------------------------+------------------------+------------------------+ |
| |name |email |organization | |
| +------------------------+------------------------+------------------------+ |
| | aaa| aaa@gmail.com| Google| |
| | bbb| BBB@outlook.com| Microsoft| |
| | ccc| ccc@gmail.com| Google| |
| | eee| eee@wisc.edu| UW Madison| |
| | eef| fff@cs.wisc.edu| UW Madison CS| |
| +------------------------+------------------------+------------------------+ |
| == |