This is an automated email from the ASF dual-hosted git repository.
yamamuro pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new da8c7b8 [SPARK-31556][SQL][DOCS] Document LIKE clause in SQL Reference
da8c7b8 is described below
commit da8c7b8ceffa1566ae35280a2d1c3abcbff47542
Author: Huaxin Gao <[email protected]>
AuthorDate: Wed Apr 29 09:17:23 2020 +0900
[SPARK-31556][SQL][DOCS] Document LIKE clause in SQL Reference
### What changes were proposed in this pull request?
Document LIKE clause in SQL Reference
### Why are the changes needed?
To make SQL Reference complete
### Does this PR introduce any user-facing change?
Yes
<img width="1050" alt="Screen Shot 2020-04-25 at 5 49 57 PM"
src="https://user-images.githubusercontent.com/13592258/80294346-5babab80-871d-11ea-8ac9-51bbab0aca88.png";>
<img width="1050" alt="Screen Shot 2020-04-25 at 5 50 24 PM"
src="https://user-images.githubusercontent.com/13592258/80294347-5ea69c00-871d-11ea-8c51-7a90ee20f7da.png";>
<img width="1050" alt="Screen Shot 2020-04-25 at 5 50 42 PM"
src="https://user-images.githubusercontent.com/13592258/80294351-61a18c80-871d-11ea-9e75-e3345d2f52f5.png";>
### How was this patch tested?
Manually build and check
Closes #28332 from huaxingao/where_clause.
Authored-by: Huaxin Gao <[email protected]>
Signed-off-by: Takeshi Yamamuro <[email protected]>
(cherry picked from commit d34cb59fb311c3d700e4f4f877b61b17cea313ee)
Signed-off-by: Takeshi Yamamuro <[email protected]>
---
docs/_data/menu-sql.yaml | 2 +
docs/sql-ref-syntax-aux-show-databases.md | 13 +++-
docs/sql-ref-syntax-aux-show-functions.md | 8 +-
docs/sql-ref-syntax-aux-show-table.md | 14 ++--
docs/sql-ref-syntax-aux-show-tables.md | 10 +--
docs/sql-ref-syntax-aux-show-views.md | 12 +--
docs/sql-ref-syntax-qry-explain.md | 2 +-
docs/sql-ref-syntax-qry-select-like.md | 120 ++++++++++++++++++++++++++++++
8 files changed, 154 insertions(+), 27 deletions(-)
diff --git a/docs/_data/menu-sql.yaml b/docs/_data/menu-sql.yaml
index 1097079..dfe4cfa 100644
--- a/docs/_data/menu-sql.yaml
+++ b/docs/_data/menu-sql.yaml
@@ -170,6 +170,8 @@
url: sql-ref-syntax-qry-select-inline-table.html
- text: Common Table Expression
url: sql-ref-syntax-qry-select-cte.html
+ - text: LIKE Predicate
+ url: sql-ref-syntax-qry-select-like.html
- text: Window Function
url: sql-ref-syntax-qry-window.html
- text: EXPLAIN
diff --git a/docs/sql-ref-syntax-aux-show-databases.md
b/docs/sql-ref-syntax-aux-show-databases.md
index 0ed3452..3599009 100644
--- a/docs/sql-ref-syntax-aux-show-databases.md
+++ b/docs/sql-ref-syntax-aux-show-databases.md
@@ -29,16 +29,21 @@ and mean the same thing.
### Syntax
{% highlight sql %}
-SHOW { DATABASES | SCHEMAS } [ LIKE string_pattern ]
+SHOW { DATABASES | SCHEMAS } [ LIKE regex_pattern ]
{% endhighlight %}
### Parameters
<dl>
- <dt><code><em>LIKE string_pattern</em></code></dt>
+ <dt><code><em>regex_pattern</em></code></dt>
<dd>
- Specifies a string pattern that is used to match the databases in the
system. In
- the specified string pattern <code>'*'</code> matches any number of
characters.
+ Specifies a regular expression pattern that is used to filter the results
of the
+ statement.
+ <ul>
+ <li>Only <code>*</code> and <code>|</code> are allowed as wildcard
pattern.</li>
+ <li>Excluding <code>*</code> and <code>|</code>, the remaining pattern
follows the regular expression semantics.</li>
+ <li>The leading and trailing blanks are trimmed in the input pattern
before processing. The pattern match is case-insensitive.</li>
+ </ul>
</dd>
</dl>
diff --git a/docs/sql-ref-syntax-aux-show-functions.md
b/docs/sql-ref-syntax-aux-show-functions.md
index da33d99..ed22a3a 100644
--- a/docs/sql-ref-syntax-aux-show-functions.md
+++ b/docs/sql-ref-syntax-aux-show-functions.md
@@ -58,12 +58,12 @@ SHOW [ function_kind ] FUNCTIONS ( [ LIKE ] function_name |
regex_pattern )
</dd>
<dt><code><em>regex_pattern</em></code></dt>
<dd>
- Specifies a regular expression pattern that is used to limit the results
of the
+ Specifies a regular expression pattern that is used to filter the results
of the
statement.
<ul>
- <li>Only `*` and `|` are allowed as wildcard pattern.</li>
- <li>Excluding `*` and `|` the remaining pattern follows the regex
semantics.</li>
- <li>The leading and trailing blanks are trimmed in the input pattern
before processing.</li>
+ <li>Only <code>*</code> and <code>|</code> are allowed as wildcard
pattern.</li>
+ <li>Excluding <code>*</code> and <code>|</code>, the remaining pattern
follows the regular expression semantics.</li>
+ <li>The leading and trailing blanks are trimmed in the input pattern
before processing. The pattern match is case-insensitive.</li>
</ul>
</dd>
</dl>
diff --git a/docs/sql-ref-syntax-aux-show-table.md
b/docs/sql-ref-syntax-aux-show-table.md
index 1aa44d3..c688a99 100644
--- a/docs/sql-ref-syntax-aux-show-table.md
+++ b/docs/sql-ref-syntax-aux-show-table.md
@@ -33,7 +33,7 @@ cannot be used with a partition specification.
### Syntax
{% highlight sql %}
-SHOW TABLE EXTENDED [ IN | FROM database_name ] LIKE
'identifier_with_wildcards'
+SHOW TABLE EXTENDED [ IN | FROM database_name ] LIKE regex_pattern
[ partition_spec ]
{% endhighlight %}
@@ -44,15 +44,15 @@ SHOW TABLE EXTENDED [ IN | FROM database_name ] LIKE
'identifier_with_wildcards'
<dd>
Specifies database name. If not provided, will use the current database.
</dd>
- <dt><code><em>LIKE string_pattern</em></code></dt>
+ <dt><code><em>regex_pattern</em></code></dt>
<dd>
Specifies the regular expression pattern that is used to filter out
unwanted tables.
- <ul>
- <li> Except for `*` and `|` character, the pattern works like a
regex.</li>
- <li> `*` alone matches 0 or more characters and `|` is used to separate
multiple different regexes,
+ <ul>
+ <li> Except for <code>*</code> and <code>|</code> character, the
pattern works like a regular expression.</li>
+ <li> <code>*</code> alone matches 0 or more characters and
<code>|</code> is used to separate multiple different regular expressions,
any of which can match. </li>
- <li> The leading and trailing blanks are trimmed in the input pattern
before processing.</li>
- </ul>
+ <li> The leading and trailing blanks are trimmed in the input pattern
before processing. The pattern match is case-insensitive.</li>
+ </ul>
</dd>
<dt><code><em>partition_spec</em></code></dt>
<dd>
diff --git a/docs/sql-ref-syntax-aux-show-tables.md
b/docs/sql-ref-syntax-aux-show-tables.md
index 0b7062e..f4b3dff 100644
--- a/docs/sql-ref-syntax-aux-show-tables.md
+++ b/docs/sql-ref-syntax-aux-show-tables.md
@@ -29,7 +29,7 @@ current database.
### Syntax
{% highlight sql %}
-SHOW TABLES [ { FROM | IN } database_name ] [ LIKE 'regex_pattern' ]
+SHOW TABLES [ { FROM | IN } database_name ] [ LIKE regex_pattern ]
{% endhighlight %}
### Parameters
@@ -39,14 +39,14 @@ SHOW TABLES [ { FROM | IN } database_name ] [ LIKE
'regex_pattern' ]
<dd>
Specifies the database name from which tables are listed.
</dd>
- <dt><code><em>LIKE regex_pattern</em></code></dt>
+ <dt><code><em>regex_pattern</em></code></dt>
<dd>
Specifies the regular expression pattern that is used to filter out
unwanted tables.
<ul>
- <li> Except for `*` and `|` character, the pattern works like a
regex.</li>
- <li> `*` alone matches 0 or more characters and `|` is used to
separate multiple different regexes,
+ <li> Except for <code>*</code> and <code>|</code> character, the
pattern works like a regular expression.</li>
+ <li> <code>*</code> alone matches 0 or more characters and
<code>|</code> is used to separate multiple different regular expressions,
any of which can match. </li>
- <li> The leading and trailing blanks are trimmed in the input
pattern before processing.</li>
+ <li> The leading and trailing blanks are trimmed in the input
pattern before processing. The pattern match is case-insensitive.</li>
</ul>
</dd>
diff --git a/docs/sql-ref-syntax-aux-show-views.md
b/docs/sql-ref-syntax-aux-show-views.md
index aec3716..0d9210b 100644
--- a/docs/sql-ref-syntax-aux-show-views.md
+++ b/docs/sql-ref-syntax-aux-show-views.md
@@ -30,7 +30,7 @@ regardless of a given database.
### Syntax
{% highlight sql %}
-SHOW VIEWS [ { FROM | IN } database_name ] [ LIKE 'regex_pattern' ]
+SHOW VIEWS [ { FROM | IN } database_name ] [ LIKE regex_pattern ]
{% endhighlight %}
### Parameters
@@ -39,14 +39,14 @@ SHOW VIEWS [ { FROM | IN } database_name ] [ LIKE
'regex_pattern' ]
<dd>
Specifies the database name from which views are listed.
</dd>
- <dt><code><em>LIKE regex_pattern</em></code></dt>
+ <dt><code><em>regex_pattern</em></code></dt>
<dd>
- Specifies the regular expression pattern that is used to filter out
unwanted views.
+ Specifies the regular expression pattern that is used to filter out
unwanted views.
<ul>
- <li> Except for `*` and `|` character, the pattern works like a
regex.</li>
- <li> `*` alone matches 0 or more characters and `|` is used to
separate multiple different regexes,
+ <li>Except for <code>*</code> and <code>|</code> character, the
pattern works like a regular expression.</li>
+ <li><code>*</code> alone matches 0 or more characters and
<code>|</code> is used to separate multiple different regular expressions,
any of which can match. </li>
- <li> The leading and trailing blanks are trimmed in the input
pattern before processing.</li>
+ <li>The leading and trailing blanks are trimmed in the input pattern
before processing. The pattern match is case-insensitive.</li>
</ul>
</dd>
</dl>
diff --git a/docs/sql-ref-syntax-qry-explain.md
b/docs/sql-ref-syntax-qry-explain.md
index 6a7c2ac..7ad297d 100644
--- a/docs/sql-ref-syntax-qry-explain.md
+++ b/docs/sql-ref-syntax-qry-explain.md
@@ -27,7 +27,7 @@ By default, this clause provides information about a physical
plan only.
### Syntax
{% highlight sql %}
-EXPLAIN [EXTENDED | CODEGEN | COST | FORMATTED] statement
+EXPLAIN [ EXTENDED | CODEGEN | COST | FORMATTED ] statement
{% endhighlight %}
### Parameters
diff --git a/docs/sql-ref-syntax-qry-select-like.md
b/docs/sql-ref-syntax-qry-select-like.md
new file mode 100644
index 0000000..408673c
--- /dev/null
+++ b/docs/sql-ref-syntax-qry-select-like.md
@@ -0,0 +1,120 @@
+---
+layout: global
+title: LIKE Predicate
+displayTitle: LIKE Predicate
+license: |
+ 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.
+---
+
+### Description
+
+A LIKE predicate is used to search for a specific pattern.
+
+### Syntax
+
+{% highlight sql %}
+[ NOT ] { LIKE search_pattern [ ESCAPE esc_char ] | RLIKE regex_pattern }
+{% endhighlight %}
+
+### Parameters
+
+<dl>
+ <dt><code><em>search_pattern</em></code></dt>
+ <dd>
+ Specifies a string pattern to be searched by the <code>LIKE</code> clause.
It can contain special pattern-matching characters:
+ <ul>
+ <li><code>%</code></li> matches zero or more characters.
+ <li><code>_</code></li> matches exactly one character.
+ </ul>
+ </dd>
+</dl>
+<dl>
+ <dt><code><em>esc_char</em></code></dt>
+ <dd>
+ Specifies the escape character. The default escape character is
<code>\</code>.
+ </dd>
+</dl>
+<dl>
+ <dt><code><em>regex_pattern</em></code></dt>
+ <dd>
+ Specifies a regular expression search pattern to be searched by the
<code>RLIKE</code> clause.
+ </dd>
+</dl>
+
+### Examples
+
+{% highlight sql %}
+CREATE TABLE person (id INT, name STRING, age INT);
+INSERT INTO person VALUES
+ (100, 'John', 30),
+ (200, 'Mary', NULL),
+ (300, 'Mike', 80),
+ (400, 'Dan', 50),
+ (500, 'Evan_w', 16);
+
+SELECT * FROM person WHERE name LIKE 'M%';
++---+----+----+
+| id|name| age|
++---+----+----+
+|300|Mike| 80|
+|200|Mary|null|
++---+----+----+
+
+SELECT * FROM person WHERE name LIKE 'M_ry';
++---+----+----+
+| id|name| age|
++---+----+----+
+|200|Mary|null|
++---+----+----+
+
+SELECT * FROM person WHERE name NOT LIKE 'M_ry';
++---+------+---+
+| id| name|age|
++---+------+---+
+|500|Evan_W| 16|
+|300| Mike| 80|
+|100| John| 30|
+|400| Dan| 50|
++---+------+---+
+
+SELECT * FROM person WHERE name RLIKE '[MD]';
++---+----+----+
+| id|name| age|
++---+----+----+
+|300|Mike| 80|
+|400| Dan| 50|
+|200|Mary|null|
++---+----+----+
+
+SELECT * FROM person WHERE name LIKE '%\_%';
++---+------+---+
+| id| name|age|
++---+------+---+
+|500|Evan_W| 16|
++---+------+---+
+
+SELECT * FROM person WHERE name LIKE '%$_%' ESCAPE '$';
++---+------+---+
+| id| name|age|
++---+------+---+
+|500|Evan_W| 16|
++---+------+---+
+{% endhighlight %}
+
+### Related Statements
+
+ * [SELECT](sql-ref-syntax-qry-select.html)
+ * [WHERE Clause](sql-ref-syntax-qry-select-where.html)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
![https://user-images.githubusercontent.com/13592258/80294346-5babab80-871d-11ea-8ac9-51bbab0aca88.png"](https://user-images.githubusercontent.com/13592258/80294346-5babab80-871d-11ea-8ac9-51bbab0aca88.png"){kind=link}
![https://user-images.githubusercontent.com/13592258/80294347-5ea69c00-871d-11ea-8c51-7a90ee20f7da.png"](https://user-images.githubusercontent.com/13592258/80294347-5ea69c00-871d-11ea-8c51-7a90ee20f7da.png"){kind=link}
![https://user-images.githubusercontent.com/13592258/80294351-61a18c80-871d-11ea-9e75-e3345d2f52f5.png"](https://user-images.githubusercontent.com/13592258/80294351-61a18c80-871d-11ea-9e75-e3345d2f52f5.png"){kind=link}