elasticsearch/docs/reference/sql/language/index-patterns.asciidoc

[role="xpack"]
[testenv="basic"]
[[sql-index-patterns]]
== Index patterns

{es-sql} supports two types of patterns for matching multiple indices or tables:

* {es} multi-index

The {es} notation for enumerating, including or excluding <<multi-index,multi index syntax>>
is supported _as long_ as it is quoted or escaped as a table identifier.

For example:

["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs.csv-spec[showTablesEsMultiIndex]
----

Notice the pattern is surrounded by double quotes `"`. It enumerated `*` meaning all indices however
it excludes (due to `-`) all indices that start with `l`.
This notation is very convenient and powerful as it allows both inclusion and exclusion, depending on
the target naming convention.

* SQL `LIKE` notation

The common `LIKE` statement (including escaping if needed) to match a wildcard pattern, based on one `_`
or multiple `%` characters.

Using `SHOW TABLES` command again:

["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs.csv-spec[showTablesLikeWildcard]
----

The pattern matches all tables that start with `emp`. 

This command supports _escaping_ as well, for example:

["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs.csv-spec[showTablesLikeEscape]
----

Notice how now `emp%` does not match any tables because `%`, which means match zero or more characters,
has been escaped by `!` and thus becomes an regular char. And since there is no table named `emp%`,
an empty table is returned.

In a nutshell, the differences between the two type of patterns are:

[cols="^h,^,^",options="header"]
|===
| Feature | Multi index | SQL `LIKE`

| Type of quoting    | `"` | `'`
| Inclusion          | Yes | Yes
| Exclusion          | Yes | No
| Enumeration        | Yes | No
| One char pattern   | No  | `_`
| Multi char pattern | `*` | `%`
| Escaping           | No  | `ESCAPE`

|===

Which one to use, is up to you however try to stick to the same one across your queries for consistency.

NOTE: As the query type of quoting between the two patterns is fairly similar (`"` vs `'`), {es-sql} _always_
requires the keyword `LIKE` for SQL `LIKE` pattern.