Skip to main content
`Ctrl`+`K`

# 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_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.25.5. Return value

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