- Elasticsearch Guide: other versions:
- What is Elasticsearch?
- What’s new in 7.9
- Getting started with Elasticsearch
- Set up Elasticsearch
- Installing Elasticsearch
- Configuring Elasticsearch
- Setting JVM options
- Secure settings
- Auditing settings
- Circuit breaker settings
- Cluster-level shard allocation and routing settings
- Cross-cluster replication settings
- Discovery and cluster formation settings
- Field data cache settings
- HTTP
- Index lifecycle management settings
- Index management settings
- Index recovery settings
- Indexing buffer settings
- License settings
- Local gateway settings
- Logging
- Machine learning settings
- Monitoring settings
- Node
- Network settings
- Node query cache settings
- Search settings
- Security settings
- Shard request cache settings
- Snapshot lifecycle management settings
- Transforms settings
- Transport
- Thread pools
- Watcher settings
- Important Elasticsearch configuration
- Important System Configuration
- Bootstrap Checks
- Heap size check
- File descriptor check
- Memory lock check
- Maximum number of threads check
- Max file size check
- Maximum size virtual memory check
- Maximum map count check
- Client JVM check
- Use serial collector check
- System call filter check
- OnError and OnOutOfMemoryError checks
- Early-access check
- G1GC check
- All permission check
- Discovery configuration check
- Bootstrap Checks for X-Pack
- Starting Elasticsearch
- Stopping Elasticsearch
- Discovery and cluster formation
- Add and remove nodes in your cluster
- Full-cluster restart and rolling restart
- Remote clusters
- Set up X-Pack
- Configuring X-Pack Java Clients
- Plugins
- Upgrade Elasticsearch
- Index modules
- Mapping
- Text analysis
- Overview
- Concepts
- Configure text analysis
- Built-in analyzer reference
- Tokenizer reference
- Token filter reference
- Apostrophe
- ASCII folding
- CJK bigram
- CJK width
- Classic
- Common grams
- Conditional
- Decimal digit
- Delimited payload
- Dictionary decompounder
- Edge n-gram
- Elision
- Fingerprint
- Flatten graph
- Hunspell
- Hyphenation decompounder
- Keep types
- Keep words
- Keyword marker
- Keyword repeat
- KStem
- Length
- Limit token count
- Lowercase
- MinHash
- Multiplexer
- N-gram
- Normalization
- Pattern capture
- Pattern replace
- Phonetic
- Porter stem
- Predicate script
- Remove duplicates
- Reverse
- Shingle
- Snowball
- Stemmer
- Stemmer override
- Stop
- Synonym
- Synonym graph
- Trim
- Truncate
- Unique
- Uppercase
- Word delimiter
- Word delimiter graph
- Character filters reference
- Normalizers
- Index templates
- Data streams
- Ingest node
- Search your data
- Query DSL
- Aggregations
- Bucket Aggregations
- Adjacency matrix
- Auto-interval date histogram
- Children
- Composite
- Date histogram
- Date range
- Diversified sampler
- Filter
- Filters
- Geo-distance
- Geohash grid
- Geotile grid
- Global
- Histogram
- IP range
- Missing
- Nested
- Parent
- Range
- Rare terms
- Reverse nested
- Sampler
- Significant terms
- Significant text
- Terms
- Variable width histogram
- Subtleties of bucketing range fields
- Metrics Aggregations
- Pipeline Aggregations
- Bucket Aggregations
- EQL
- SQL access
- Overview
- Getting Started with SQL
- Conventions and Terminology
- Security
- SQL REST API
- SQL Translate API
- SQL CLI
- SQL JDBC
- SQL ODBC
- SQL Client Applications
- SQL Language
- Functions and Operators
- Comparison Operators
- Logical Operators
- Math Operators
- Cast Operators
- LIKE and RLIKE Operators
- Aggregate Functions
- Grouping Functions
- Date/Time and Interval Functions and Operators
- Full-Text Search Functions
- Mathematical Functions
- String Functions
- Type Conversion Functions
- Geo Functions
- Conditional Functions And Expressions
- System Functions
- Reserved keywords
- SQL Limitations
- Scripting
- ILM: Manage the index lifecycle
- Monitor a cluster
- Frozen indices
- Roll up or transform your data
- Set up a cluster for high availability
- Snapshot and restore
- Secure a cluster
- Overview
- Configuring security
- User authentication
- Built-in users
- Internal users
- Token-based authentication services
- Realms
- Realm chains
- Active Directory user authentication
- File-based user authentication
- LDAP user authentication
- Native user authentication
- OpenID Connect authentication
- PKI user authentication
- SAML authentication
- Kerberos authentication
- Integrating with other authentication systems
- Enabling anonymous access
- Controlling the user cache
- Configuring SAML single-sign-on on the Elastic Stack
- Configuring single sign-on to the Elastic Stack using OpenID Connect
- User authorization
- Built-in roles
- Defining roles
- Granting access to Stack Management features
- Security privileges
- Document level security
- Field level security
- Granting privileges for data streams and index aliases
- Mapping users and groups to roles
- Setting up field and document level security
- Submitting requests on behalf of other users
- Configuring authorization delegation
- Customizing roles and authorization
- Enabling audit logging
- Encrypting communications
- Restricting connections with IP filtering
- Cross cluster search, clients, and integrations
- Tutorial: Getting started with security
- Tutorial: Encrypting communications
- Troubleshooting
- Some settings are not returned via the nodes settings API
- Authorization exceptions
- Users command fails due to extra arguments
- Users are frequently locked out of Active Directory
- Certificate verification fails for curl on Mac
- SSLHandshakeException causes connections to fail
- Common SSL/TLS exceptions
- Common Kerberos exceptions
- Common SAML issues
- Internal Server Error in Kibana
- Setup-passwords command fails due to connection failure
- Failures due to relocation of the configuration files
- Limitations
- Alerting on cluster and index events
- Command line tools
- How To
- Glossary of terms
- REST APIs
- API conventions
- cat APIs
- cat aliases
- cat allocation
- cat anomaly detectors
- cat count
- cat data frame analytics
- cat datafeeds
- cat fielddata
- cat health
- cat indices
- cat master
- cat nodeattrs
- cat nodes
- cat pending tasks
- cat plugins
- cat recovery
- cat repositories
- cat shards
- cat segments
- cat snapshots
- cat task management
- cat templates
- cat thread pool
- cat trained model
- cat transforms
- Cluster APIs
- Cluster allocation explain
- Cluster get settings
- Cluster health
- Cluster reroute
- Cluster state
- Cluster stats
- Cluster update settings
- Nodes feature usage
- Nodes hot threads
- Nodes info
- Nodes reload secure settings
- Nodes stats
- Pending cluster tasks
- Remote cluster info
- Task management
- Voting configuration exclusions
- Cross-cluster replication APIs
- Data stream APIs
- Document APIs
- Enrich APIs
- Explore API
- Index APIs
- Add index alias
- Analyze
- Clear cache
- Clone index
- Close index
- Create index
- Delete index
- Delete index alias
- Delete component template
- Delete index template
- Delete index template (legacy)
- Flush
- Force merge
- Freeze index
- Get component template
- Get field mapping
- Get index
- Get index alias
- Get index settings
- Get index template
- Get index template (legacy)
- Get mapping
- Index alias exists
- Index exists
- Index recovery
- Index segments
- Index shard stores
- Index stats
- Index template exists
- Open index
- Put index template
- Put index template (legacy)
- Put component template
- Put mapping
- Refresh
- Rollover index
- Shrink index
- Simulate index
- Simulate template
- Split index
- Synced flush
- Type exists
- Unfreeze index
- Update index alias
- Update index settings
- Resolve index
- List dangling indices
- Import dangling index
- Delete dangling index
- Index lifecycle management API
- Ingest APIs
- Info API
- Licensing APIs
- Machine learning anomaly detection APIs
- Add events to calendar
- Add jobs to calendar
- Close jobs
- Create jobs
- Create calendar
- Create datafeeds
- Create filter
- Delete calendar
- Delete datafeeds
- Delete events from calendar
- Delete filter
- Delete forecast
- Delete jobs
- Delete jobs from calendar
- Delete model snapshots
- Delete expired data
- Estimate model memory
- Find file structure
- Flush jobs
- Forecast jobs
- Get buckets
- Get calendars
- Get categories
- Get datafeeds
- Get datafeed statistics
- Get influencers
- Get jobs
- Get job statistics
- Get machine learning info
- Get model snapshots
- Get overall buckets
- Get scheduled events
- Get filters
- Get records
- Open jobs
- Post data to jobs
- Preview datafeeds
- Revert model snapshots
- Set upgrade mode
- Start datafeeds
- Stop datafeeds
- Update datafeeds
- Update filter
- Update jobs
- Update model snapshots
- Machine learning data frame analytics APIs
- Create data frame analytics jobs
- Create trained model
- Update data frame analytics jobs
- Delete data frame analytics jobs
- Delete trained model
- Evaluate data frame analytics
- Explain data frame analytics API
- Get data frame analytics jobs
- Get data frame analytics jobs stats
- Get trained model
- Get trained model stats
- Start data frame analytics jobs
- Stop data frame analytics jobs
- Migration APIs
- Reload search analyzers
- Rollup APIs
- Search APIs
- Security APIs
- Authenticate
- Change passwords
- Clear cache
- Clear roles cache
- Clear privileges cache
- Create API keys
- Create or update application privileges
- Create or update role mappings
- Create or update roles
- Create or update users
- Delegate PKI authentication
- Delete application privileges
- Delete role mappings
- Delete roles
- Delete users
- Disable users
- Enable users
- Get API key information
- Get application privileges
- Get builtin privileges
- Get role mappings
- Get roles
- Get token
- Get users
- Grant API keys
- Has privileges
- Invalidate API key
- Invalidate token
- OpenID Connect Prepare Authentication API
- OpenID Connect authenticate API
- OpenID Connect logout API
- SAML prepare authentication API
- SAML authenticate API
- SAML logout API
- SAML invalidate API
- SSL certificate
- Snapshot and restore APIs
- Snapshot lifecycle management API
- Transform APIs
- Usage API
- Watcher APIs
- Definitions
- Breaking changes
- Release notes
- Elasticsearch version 7.9.3
- Elasticsearch version 7.9.2
- Elasticsearch version 7.9.1
- Elasticsearch version 7.9.0
- Elasticsearch version 7.8.1
- Elasticsearch version 7.8.0
- Elasticsearch version 7.7.1
- Elasticsearch version 7.7.0
- Elasticsearch version 7.6.2
- Elasticsearch version 7.6.1
- Elasticsearch version 7.6.0
- Elasticsearch version 7.5.2
- Elasticsearch version 7.5.1
- Elasticsearch version 7.5.0
- Elasticsearch version 7.4.2
- Elasticsearch version 7.4.1
- Elasticsearch version 7.4.0
- Elasticsearch version 7.3.2
- Elasticsearch version 7.3.1
- Elasticsearch version 7.3.0
- Elasticsearch version 7.2.1
- Elasticsearch version 7.2.0
- Elasticsearch version 7.1.1
- Elasticsearch version 7.1.0
- Elasticsearch version 7.0.0
- Elasticsearch version 7.0.0-rc2
- Elasticsearch version 7.0.0-rc1
- Elasticsearch version 7.0.0-beta1
- Elasticsearch version 7.0.0-alpha2
- Elasticsearch version 7.0.0-alpha1
String Functions
editString Functions
editFunctions for performing string manipulation.
ASCII
editSynopsis:
Input:
Output: integer
Description: Returns the ASCII code value of the leftmost character of string_exp
as an integer.
SELECT ASCII('Elastic'); ASCII('Elastic') ---------------- 69
BIT_LENGTH
editSynopsis:
Input:
Output: integer
Description: Returns the length in bits of the string_exp
input expression.
SELECT BIT_LENGTH('Elastic'); BIT_LENGTH('Elastic') --------------------- 56
CHAR
editSynopsis:
Input:
Output: string
Description: Returns the character that has the ASCII code value specified by the numeric input. The value should be between 0 and 255; otherwise, the return value is data source–dependent.
SELECT CHAR(69); CHAR(69) --------------- E
CHAR_LENGTH
editSynopsis:
Input:
Output: integer
Description: Returns the length in characters of the input, if the string expression is of a character data type; otherwise, returns the length in bytes of the string expression (the smallest integer not less than the number of bits divided by 8).
SELECT CHAR_LENGTH('Elastic'); CHAR_LENGTH('Elastic') ---------------------- 7
CONCAT
editSynopsis:
Input:
Output: string
Description: Returns a character string that is the result of concatenating string_exp1
to string_exp2
. If one of the string is NULL
, the other string will be returned.
SELECT CONCAT('Elasticsearch', ' SQL'); CONCAT('Elasticsearch', ' SQL') ------------------------------- Elasticsearch SQL
INSERT
editSynopsis:
Input:
Output: string
Description: Returns a string where length
characters have been deleted from source
, beginning at start
, and where replacement
has been inserted into source
, beginning at start
.
SELECT INSERT('Elastic ', 8, 1, 'search'); INSERT('Elastic ', 8, 1, 'search') ---------------------------------- Elasticsearch
LCASE
editSynopsis:
Input:
Output: string
Description: Returns a string equal to that in string_exp
, with all uppercase characters converted to lowercase.
SELECT LCASE('Elastic'); LCASE('Elastic') ---------------- elastic
LEFT
editSynopsis:
Input:
Output: string
Description: Returns the leftmost count characters of string_exp
.
SELECT LEFT('Elastic',3); LEFT('Elastic',3) ----------------- Ela
LENGTH
editSynopsis:
Input:
Output: integer
Description: Returns the number of characters in string_exp
, excluding trailing blanks.
SELECT LENGTH('Elastic '); LENGTH('Elastic ') -------------------- 7
LOCATE
editSynopsis:
Input:
Output: integer
Description: Returns the starting position of the first occurrence of pattern
within source
. The search for the first occurrence of pattern
begins with the first character position in source
unless the optional argument, start
, is specified. If start
is specified, the search begins with the character position indicated by the value of start
. The first character position in source
is indicated by the value 1. If pattern
is not found within source
, the value 0 is returned.
SELECT LOCATE('a', 'Elasticsearch'); LOCATE('a', 'Elasticsearch') ---------------------------- 3
SELECT LOCATE('a', 'Elasticsearch', 5); LOCATE('a', 'Elasticsearch', 5) ------------------------------- 10
LTRIM
editSynopsis:
Input:
Output: string
Description: Returns the characters of string_exp
, with leading blanks removed.
SELECT LTRIM(' Elastic'); LTRIM(' Elastic') ------------------- Elastic
OCTET_LENGTH
editSynopsis:
Input:
Output: integer
Description: Returns the length in bytes of the string_exp
input expression.
SELECT OCTET_LENGTH('Elastic'); OCTET_LENGTH('Elastic') ----------------------- 7
POSITION
editSynopsis:
Input:
Output: integer
Description: Returns the position of the string_exp1
in string_exp2
. The result is an exact numeric.
SELECT POSITION('Elastic', 'Elasticsearch'); POSITION('Elastic', 'Elasticsearch') ------------------------------------ 1
REPEAT
editSynopsis:
Input:
Output: string
Description: Returns a character string composed of string_exp
repeated count
times.
SELECT REPEAT('La', 3); REPEAT('La', 3) ---------------- LaLaLa
REPLACE
editSynopsis:
Input:
Output: string
Description: Search source
for occurrences of pattern
, and replace with replacement
.
SELECT REPLACE('Elastic','El','Fant'); REPLACE('Elastic','El','Fant') ------------------------------ Fantastic
RIGHT
editSynopsis:
Input:
Output: string
Description: Returns the rightmost count characters of string_exp
.
SELECT RIGHT('Elastic',3); RIGHT('Elastic',3) ------------------ tic
RTRIM
editSynopsis:
Input:
Output: string
Description: Returns the characters of string_exp
with trailing blanks removed.
SELECT RTRIM('Elastic '); RTRIM('Elastic ') ------------------- Elastic
SPACE
editSynopsis:
Input:
Output: string
Description: Returns a character string consisting of count
spaces.
SELECT SPACE(3); SPACE(3) ---------------
STARTS_WITH
editSynopsis:
Input:
Output: boolean value
Description: Returns true
if the source expression starts with the specified pattern, false
otherwise. The matching is case sensitive.
If either parameters is null
, the function returns null
.
SELECT STARTS_WITH('Elasticsearch', 'Elastic'); STARTS_WITH('Elasticsearch', 'Elastic') -------------------------------- true
SELECT STARTS_WITH('Elasticsearch', 'ELASTIC'); STARTS_WITH('Elasticsearch', 'ELASTIC') -------------------------------- false
SUBSTRING
editSynopsis:
Input:
Output: string
Description: Returns a character string that is derived from source
, beginning at the character position specified by start
for length
characters.
SELECT SUBSTRING('Elasticsearch', 0, 7); SUBSTRING('Elasticsearch', 0, 7) -------------------------------- Elastic
TRIM
editSynopsis:
Input:
Output: string
Description: Returns the characters of string_exp
, with leading and trailing blanks removed.
SELECT TRIM(' Elastic ') AS trimmed; trimmed -------------- Elastic
UCASE
editSynopsis:
Input:
Output: string
Description: Returns a string equal to that of the input, with all lowercase characters converted to uppercase.
SELECT UCASE('Elastic'); UCASE('Elastic') ---------------- ELASTIC
On this page