0001 ---
0002 layout: global
0003 title: LIKE Predicate
0004 displayTitle: LIKE Predicate
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 A LIKE predicate is used to search for a specific pattern.
0025
0026 ### Syntax
0027
0028 ```sql
0029 [ NOT ] { LIKE search_pattern [ ESCAPE esc_char ] | RLIKE regex_pattern }
0030 ```
0031
0032 ### Parameters
0033
0034 * **search_pattern**
0035
0036 Specifies a string pattern to be searched by the `LIKE` clause. It can contain special pattern-matching characters:
0037
0038 * `%` matches zero or more characters.
0039 * `_` matches exactly one character.
0040
0041 * **esc_char**
0042
0043 Specifies the escape character. The default escape character is `\`.
0044
0045 * **regex_pattern**
0046
0047 Specifies a regular expression search pattern to be searched by the `RLIKE` clause.
0048
0049 ### Examples
0050
0051 ```sql
0052 CREATE TABLE person (id INT, name STRING, age INT);
0053 INSERT INTO person VALUES
0054 (100, 'John', 30),
0055 (200, 'Mary', NULL),
0056 (300, 'Mike', 80),
0057 (400, 'Dan', 50),
0058 (500, 'Evan_w', 16);
0059
0060 SELECT * FROM person WHERE name LIKE 'M%';
0061 +---+----+----+
0062 | id|name| age|
0063 +---+----+----+
0064 |300|Mike| 80|
0065 |200|Mary|null|
0066 +---+----+----+
0067
0068 SELECT * FROM person WHERE name LIKE 'M_ry';
0069 +---+----+----+
0070 | id|name| age|
0071 +---+----+----+
0072 |200|Mary|null|
0073 +---+----+----+
0074
0075 SELECT * FROM person WHERE name NOT LIKE 'M_ry';
0076 +---+------+---+
0077 | id| name|age|
0078 +---+------+---+
0079 |500|Evan_W| 16|
0080 |300| Mike| 80|
0081 |100| John| 30|
0082 |400| Dan| 50|
0083 +---+------+---+
0084
0085 SELECT * FROM person WHERE name RLIKE 'M+';
0086 +---+----+----+
0087 | id|name| age|
0088 +---+----+----+
0089 |300|Mike| 80|
0090 |200|Mary|null|
0091 +---+----+----+
0092
0093 SELECT * FROM person WHERE name LIKE '%\_%';
0094 +---+------+---+
0095 | id| name|age|
0096 +---+------+---+
0097 |500|Evan_W| 16|
0098 +---+------+---+
0099
0100 SELECT * FROM person WHERE name LIKE '%$_%' ESCAPE '$';
0101 +---+------+---+
0102 | id| name|age|
0103 +---+------+---+
0104 |500|Evan_W| 16|
0105 +---+------+---+
0106 ```
0107
0108 ### Related Statements
0109
0110 * [SELECT](sql-ref-syntax-qry-select.html)
0111 * [WHERE Clause](sql-ref-syntax-qry-select-where.html)