7.15.25. string_substring¶
New in version 6.0.7.
7.15.25.1. Summary¶
string_substring extracts a substring of a string by position.
To enable this function, register functions/string plugin by following the command:
plugin_register functions/string
7.15.25.2. Syntax¶
string_substring requires two to four parameters.
string_substring(target, nth[, options])
string_substring(target, nth, length[, options])
options uses the following format. All of key-value pairs are optional:
{
"default_value": default_value
}
7.15.25.3. Usage¶
Here are a schema definition and sample data to show usage.
Sample schema:
Execution example:
plugin_register functions/string
# [[0, 1337566253.89858, 0.000355720520019531], true]
table_create Memos TABLE_HASH_KEY ShortText
# [[0, 1337566253.89858, 0.000355720520019531], true]
Sample data:
Execution example:
load --table Memos
[
{"_key": "Groonga"}
]
# [[0, 1337566253.89858, 0.000355720520019531], 1]
Here is a simple example.
Execution example:
select Memos --output_columns '_key, string_substring(_key, 2, 3)'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 1
# ],
# [
# [
# "_key",
# "ShortText"
# ],
# [
# "string_substring",
# null
# ]
# ],
# [
# "Groonga",
# "oon"
# ]
# ]
# ]
# ]
In the following example, specifying the negative value for nth.
Execution example:
select Memos --output_columns '_key, string_substring(_key, -3, 2)'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 1
# ],
# [
# [
# "_key",
# "ShortText"
# ],
# [
# "string_substring",
# null
# ]
# ],
# [
# "Groonga",
# "ng"
# ]
# ]
# ]
# ]
In the following example, specifying the default value.
Execution example:
select Memos --output_columns '_key, string_substring(_key, 50, 1, { "default_value" : "default" })'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 1
# ],
# [
# [
# "_key",
# "ShortText"
# ],
# [
# "string_substring",
# null
# ]
# ],
# [
# "Groonga",
# "default"
# ]
# ]
# ]
# ]
You can specify string literal instead of column.
Execution example:
select Memos --output_columns 'string_substring("Groonga", 2, 3)'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 1
# ],
# [
# [
# "string_substring",
# null
# ]
# ],
# [
# "oon"
# ]
# ]
# ]
# ]
7.15.25.4. Parameters¶
7.15.25.4.1. Required parameters¶
7.15.25.4.1.1. target¶
Specify a string literal or a string type column.
7.15.25.4.1.2. nth¶
Specify a 0-based index number of charactors where to start the extraction from target.
If you specify a negative value, it counts from the end of target.
7.15.25.4.2. Optional parameters¶
7.15.25.4.2.1. length¶
Specify a number of characters to extract from nth.
If you omit or specify a negative value, this function extracts from nth to the end.
7.15.25.4.2.2. options¶
New in version 11.0.3.
Specify the following key.
default_valueSpecify a string to be returned when a substring is an empty string except when specifying 0 for
length.The default is an empty string.
7.15.25.5. Return value¶
string_substring returns a substring extracted under the specified conditions from target.