<commit_before>.. highlight:: psql
.. _scalar:

================
Scalar functions
================

Scalar functions return a single value.

.. rubric:: Table of contents

.. contents::
   :local:

String functions
================

.. _scalar_concat:

``concat('first_arg', second_arg, [ parameter , ... ])``
--------------------------------------------------------

Concatenates a variable number of arguments into a single string. It ignores
``NULL`` values.

Returns: ``text``

::

    cr> select concat('foo', null, 'bar');
    +----------------------------+
    | concat('foo', NULL, 'bar') |
    +----------------------------+
    | foobar                     |
    +----------------------------+
    SELECT 1 row in set (... sec)

You can also use the ``||`` operator::

    cr> select 'foo' || 'bar';
    +----------------------+
    | concat('foo', 'bar') |
    +----------------------+
    | foobar               |
    +----------------------+
    SELECT 1 row in set (... sec)

``format('format_string', parameter, [ parameter , ... ])``
-----------------------------------------------------------

Formats a string similar to the C function ``printf``. For details about the
format string syntax, see `formatter`_

Returns: ``text``

::

    cr> select format('%s.%s', schema_name, table_name) from sys.shards
    ... where table_name = 'locations'
    ... limit 1;
    +------------------------------------------+
    | format('%s.%s', schema_name, table_name) |
    +------------------------------------------+
    | doc.locations                            |
    +------------------------------------------+
    SELECT 1 row in set (... sec)

::

    cr> select format('%tY', date) from locations
    ... group by format('%tY', date)
    ... order by 1;
    +---------------------+
    | format('%tY', date) |
    +---------------------+
    |                1979 |
    |                2013 |
    +---------------------+
    SELECT 2 rows in set (... sec)

``substr('string', from, [ count ])``
-------------------------------------

Extracts a part of a string. ``from`` specifies where to start and ``count``
the length of the part.

Returns: ``text``

::

    cr> select substr('crate.io', 3, 2);
    +--------------------------+
    | substr('crate.io', 3, 2) |
    +--------------------------+
    | at                       |
    +--------------------------+
    SELECT 1 row in set (... sec)

.. _scalar_char_length:

``char_length('string')``
-------------------------

Counts the number of characters in a string.

Returns: ``integer``

::

    cr> select char_length('crate.io');
    +-------------------------+
    | char_length('crate.io') |
    +-------------------------+
    |                       8 |
    +-------------------------+
    SELECT 1 row in set (... sec)

Each character counts only once, regardless of its byte size.

::

    cr> select char_length('©rate.io');
    +-------------------------+
    | char_length('©rate.io') |
    +-------------------------+
    |                       8 |
    +-------------------------+
    SELECT 1 row in set (... sec)

.. _scalar_bit_length:

``bit_length('string')``
------------------------

Counts the number of bits in a string.

Returns: ``integer``

.. NOTE::

    CrateDB uses UTF-8 encoding internally, which uses between 1 and 4 bytes
    per character.

::

    cr> select bit_length('crate.io');
    +------------------------+
    | bit_length('crate.io') |
    +------------------------+
    |                     64 |
    +------------------------+
    SELECT 1 row in set (... sec)

::

    cr> select bit_length('©rate.io');
    +------------------------+
    | bit_length('©rate.io') |
    +------------------------+
    |                     72 |
    +------------------------+
    SELECT 1 row in set (... sec)

.. _scalar_octet_length:

``octet_length('string')``
--------------------------

Counts the number of bytes (octets) in a string.

Returns: ``integer``

::

    cr> select octet_length('crate.io');
    +--------------------------+
    | octet_length('crate.io') |
    +--------------------------+
    |                        8 |
    +--------------------------+
    SELECT 1 row in set (... sec)

::

    cr> select octet_length('©rate.io');
    +--------------------------+
    | octet_length('©rate.io') |
    +--------------------------+
    |                        9 |
    +--------------------------+
    SELECT 1 row in set (... sec)

.. _scalar_ascii:

``ascii(string)``
-----------------

Returns the ASCII code of the first character. For UTF-8 returns the Unicode
code point of the characters.

Returns: ``int``

::

    cr> SELECT ascii('a') AS a, ascii('🎈') AS b;<commit_msg>Minor copyedit<commit_after>.. highlight:: psql
.. _scalar:

================
Scalar functions
================

Scalar functions return a single value.

.. rubric:: Table of contents

.. contents::
   :local:

String functions
================

.. _scalar_concat:

``concat('first_arg', second_arg, [ parameter , ... ])``
--------------------------------------------------------

Concatenates a variable number of arguments into a single string. It ignores
``NULL`` values.

Returns: ``text``

::

    cr> select concat('foo', null, 'bar');
    +----------------------------+
    | concat('foo', NULL, 'bar') |
    +----------------------------+
    | foobar                     |
    +----------------------------+
    SELECT 1 row in set (... sec)

You can also use the ``||`` operator::

    cr> select 'foo' || 'bar';
    +----------------------+
    | concat('foo', 'bar') |
    +----------------------+
    | foobar               |
    +----------------------+
    SELECT 1 row in set (... sec)

``format('format_string', parameter, [ parameter , ... ])``
-----------------------------------------------------------

Formats a string similar to the C function ``printf``. For details about the
format string syntax, see `formatter`_

Returns: ``text``

::

    cr> select format('%s.%s', schema_name, table_name) from sys.shards
    ... where table_name = 'locations'
    ... limit 1;
    +------------------------------------------+
    | format('%s.%s', schema_name, table_name) |
    +------------------------------------------+
    | doc.locations                            |
    +------------------------------------------+
    SELECT 1 row in set (... sec)

::

    cr> select format('%tY', date) from locations
    ... group by format('%tY', date)
    ... order by 1;
    +---------------------+
    | format('%tY', date) |
    +---------------------+
    |                1979 |
    |                2013 |
    +---------------------+
    SELECT 2 rows in set (... sec)

``substr('string', from, [ count ])``
-------------------------------------

Extracts a part of a string. ``from`` specifies where to start and ``count``
the length of the part.

Returns: ``text``

::

    cr> select substr('crate.io', 3, 2);
    +--------------------------+
    | substr('crate.io', 3, 2) |
    +--------------------------+
    | at                       |
    +--------------------------+
    SELECT 1 row in set (... sec)

.. _scalar_char_length:

``char_length('string')``
-------------------------

Counts the number of characters in a string.

Returns: ``integer``

::

    cr> select char_length('crate.io');
    +-------------------------+
    | char_length('crate.io') |
    +-------------------------+
    |                       8 |
    +-------------------------+
    SELECT 1 row in set (... sec)

Each character counts only once, regardless of its byte size.

::

    cr> select char_length('©rate.io');
    +-------------------------+
    | char_length('©rate.io') |
    +-------------------------+
    |                       8 |
    +-------------------------+
    SELECT 1 row in set (... sec)

.. _scalar_bit_length:

``bit_length('string')``
------------------------

Counts the number of bits in a string.

Returns: ``integer``

.. NOTE::

    CrateDB uses UTF-8 encoding internally, which uses between 1 and 4 bytes
    per character.

::

    cr> select bit_length('crate.io');
    +------------------------+
    | bit_length('crate.io') |
    +------------------------+
    |                     64 |
    +------------------------+
    SELECT 1 row in set (... sec)

::

    cr> select bit_length('©rate.io');
    +------------------------+
    | bit_length('©rate.io') |
    +------------------------+
    |                     72 |
    +------------------------+
    SELECT 1 row in set (... sec)

.. _scalar_octet_length:

``octet_length('string')``
--------------------------

Counts the number of bytes (octets) in a string.

Returns: ``integer``

::

    cr> select octet_length('crate.io');
    +--------------------------+
    | octet_length('crate.io') |
    +--------------------------+
    |                        8 |
    +--------------------------+
    SELECT 1 row in set (... sec)

::

    cr> select octet_length('©rate.io');
    +--------------------------+
    | octet_length('©rate.io') |
    +--------------------------+
    |                        9 |
    +--------------------------+
    SELECT 1 row in set (... sec)

.. _scalar_ascii:

``ascii(string)``
-----------------

Returns the ASCII code of the first character. For UTF-8, returns the Unicode
code point of the characters.

Returns: ``int``

::

    cr> SELECT ascii('a') AS a, ascii('🎈') AS b;
{'fraction_non_alphanumeric': 0.36950053134962807, 'fraction_numerical': 0.007863974495217854, 'mean_word_length': 1.0160668380462725, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 27, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7369043', 'n_tokens_mistral': 2627, 'n_tokens_neox': 2417, 'n_words': 676}