7.15.27. string_substring#

Added in version 6.0.7.

7.15.27.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.27.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.27.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.27.4. Parameters#

7.15.27.4.1. Required parameters#

7.15.27.4.1.1. target#

Specify a string literal or a string type column.

7.15.27.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.27.4.2. Optional parameters#

7.15.27.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.27.4.2.2. options#

Added in version 11.0.3.

Specify the following key.

default_value

Specify 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.27.5. Return value#

string_substring returns a substring extracted under the specified conditions from target.