Hologres:String functions

String concatenation, splitting, and padding

Other string functions

STRING \|\| STRING

Concatenates two strings.

Syntax

<string_value1> TEXT || <string_value2> TEXT

Parameters

Return type: TEXT

Example

-- Returns: HologreSQL
SELECT 'Holo' || 'greSQL';

CONCAT

Concatenates two or more strings into one. NULL arguments are ignored.

Syntax

CONCAT(<str1> TEXT, <str2> TEXT, ..., <strN> TEXT)

Parameters

Return type: TEXT

Example

SELECT CONCAT('abcde', 2, NULL, 22);

Result:

concat
--------
abcde222

CONCAT_WS

Concatenates all arguments after the first, using the first argument as a separator.

Syntax

CONCAT_WS(<sep> TEXT, str "any" [, str "any" [, ...]])

The any type indicates that all data types are accepted.

Parameters

Required:

sep: The separator to insert between each string. If sep is NULL, the function returns NULL.

Optional:

str: The strings to concatenate. NULL string arguments are ignored.

Return type: TEXT

Example

SELECT CONCAT_WS(',', 'abcde', 2, NULL, 22);

Result:

concat_ws
----------
abcde,2,22

LISTAGG

Aggregates values from the same column across multiple rows into a single string. This is an Oracle-compatible function that requires the orafce extension. For setup instructions, see Oracle-compatible functions.

Syntax

LISTAGG(<str1> TEXT [, <str2> TEXT])

Parameters

Return type: TEXT

Examples

-- Returns: hologres
SELECT LISTAGG(t) FROM (VALUES('holo'), ('gres')) AS l(t);

-- Returns: holo.gres
SELECT LISTAGG(t, '.') FROM (VALUES('holo'), ('gres')) AS l(t);

STRING_AGG

Aggregates values from the same column across multiple rows into a single string.

Syntax

STRING_AGG(expression [order_by_clause]) [FILTER (WHERE filter_clause)]

The FILTER clause is supported in Hologres V1.3 and later. To upgrade your instance, see Upgrade an instance. For support, join the Hologres DingTalk group. For instructions, see How do I get more online support?.

Parameters

Return type: TEXT

Examples

Example 1: Aggregate all city names into a single string.

CREATE TABLE city_test (
    country text,
    city text
);

INSERT INTO city_test
    VALUES ('China', 'Shanghai'), ('China', 'Taiwan'), ('Japan', 'Tokyo'), ('France', 'Paris'), ('UK', 'London');

SELECT STRING_AGG(city, ',') FROM city_test;

Result:

string_agg
-------------------------------
Shanghai,Taiwan,Tokyo,Paris,London

Example 2: Aggregate names where student_id > 2.

CREATE TABLE name_text (
    student_id int,
    name text
);

INSERT INTO name_text
    VALUES (1, 'Zhang San'), (2, 'Li Si'), (3, 'Wang Wu'), (4, 'Zhou Liu');

SELECT STRING_AGG(name, ',') FILTER (WHERE student_id > 2) FROM name_text;

Result:

string_agg
-----------
Wang Wu,Zhou Liu

LEFT

Extracts a specified number of characters from the beginning of a string.

Syntax

LEFT(<str> TEXT, <num> INT)

Parameters

Return type: TEXT

Example

SELECT LEFT('hologres', 4);

Result:

left
----
holo

RIGHT

Extracts a specified number of characters from the end of a string.

Syntax

RIGHT(<str> TEXT, <num> INT)

Parameters

Return type: TEXT

Example

SELECT RIGHT('hologres', 4);

Result:

right
-----
gres

SPLIT_PART

Splits a string by a delimiter and returns the *n*th segment.

Syntax

SPLIT_PART(<str> TEXT, <delimiter> TEXT, <num> INT)

Parameters

Return type: TEXT

Usage notes

• If num is greater than the number of segments, the function returns NULL.

Example

CREATE TABLE split_part_test (
    a text
);

INSERT INTO split_part_test VALUES ('a/b/c/d/e'), ('a1/b1/c1/d1/e1');

SELECT split_part(a, '/', 2) FROM split_part_test;

Result:

split_part
----------
b
b1

SUBSTRING

Extracts a substring from a target string. Supports three forms: position-based, POSIX regular expression, and SQL regular expression.

Syntax

Position-based:

SUBSTRING(<str> TEXT [FROM <num_start> INT] [FOR <num_end> INT])

POSIX regular expression:

SUBSTRING(<str> TEXT FROM <pattern> TEXT)

SQL regular expression:

SUBSTRING(<str> TEXT FROM <pattern> TEXT FOR <escape> TEXT)

Parameters

Return type: TEXT

Examples

Example 1: Extract a substring by position.

SELECT SUBSTRING('Thomas' FROM 2 FOR 3);

Result:

substring
---------
hom

Example 2: Extract a substring matching a POSIX regular expression.

SELECT SUBSTRING('Thomas' FROM '...$');

Result:

substring
---------
mas

Example 3: Extract a substring matching an SQL regular expression.

SELECT SUBSTRING('Thomas' FROM '%#"o_a#"_' FOR '#');

Result:

substring
---------
oma

SUBSTR

Extracts a substring from a starting position. This is an Oracle-compatible function that requires the orafce extension. For setup instructions, see Oracle-compatible functions.

Syntax

Extract from a starting position to the end:

SUBSTR(<str> TEXT, <num_start> INTEGER)

Extract a fixed-length substring:

SUBSTR(<str> TEXT, <num_start> INTEGER, <len> INTEGER)

Parameters

Return type: TEXT

Examples

Example 1: Extract from the fourth character to the end.

-- Returns: ogres
SELECT SUBSTR('Hologres', 4);

Example 2: Extract five characters starting from the second character.

-- Returns: ologr
SELECT SUBSTR('Hologres', 2, 5);

REGEXP_MATCH

Matches a string against a regular expression and returns the matches as an array.

Syntax

REGEXP_MATCH(<str> TEXT, <pattern> TEXT)

Parameters

Return type: ARRAY

Example

SELECT regexp_match('foobarbequebaz', '(bar)(beque)');

Result:

regexp_match
------------
{bar,beque}

REGEXP_SPLIT_TO_TABLE

Splits a string using a regular expression and returns each segment as a separate row. Use this function to expand delimited values into rows.

Syntax

REGEXP_SPLIT_TO_TABLE(<str> TEXT, <pattern> TEXT)

Parameters

Return type: TEXT (one row per segment)

Example

CREATE TABLE interests_test (
    name text,
    intrests text
);

INSERT INTO interests_test
    VALUES ('Zhang San', 'singing,dancing'), ('Li Si', 'soccer,running,drawing'), ('Wang Wu', 'flower arranging,calligraphy,playing piano,sleeping');

SELECT name, REGEXP_SPLIT_TO_TABLE(intrests, ',') FROM interests_test;

Result:

name      | regexp_split_to_table
----------+----------------------
Zhang San | singing
Zhang San | dancing
Li Si     | soccer
Li Si     | running
Li Si     | drawing
Wang Wu   | flower arranging
Wang Wu   | calligraphy
Wang Wu   | playing piano
Wang Wu   | sleeping

REGEXP_SPLIT_TO_ARRAY

Splits a string using a regular expression and returns the segments as an array.

Syntax

REGEXP_SPLIT_TO_ARRAY(<str> TEXT, <pattern> TEXT)

Parameters

Return type: ARRAY

Example

CREATE TABLE interests_test (
    name text,
    intrests text
);

INSERT INTO interests_test
    VALUES ('Zhang San', 'singing,dancing'), ('Li Si', 'soccer,running,drawing'), ('Wang Wu', 'flower arranging,calligraphy,playing piano,sleeping');

SELECT name, REGEXP_SPLIT_TO_ARRAY(intrests, ',') FROM interests_test;

Result:

name      | regexp_split_to_array
----------+----------------------------------------------------------
Zhang San | {singing,dancing}
Li Si     | {soccer,running,drawing}
Wang Wu   | {"flower arranging",calligraphy,"playing piano",sleeping}

TRIM

Removes specified characters from one or both ends of a string.

Syntax

TRIM([LEADING | TRAILING | BOTH] [<characters> TEXT] FROM <string> TEXT)

Parameters

Return type: TEXT

Example

-- Returns: Tom
SELECT TRIM(BOTH 'xyz' FROM 'yxTomxx');

BTRIM

Removes specified characters from both ends of a string.

Syntax

BTRIM(<str> TEXT [, <characters> TEXT])

Parameters

Return type: TEXT

Example

-- Returns: trim
SELECT BTRIM('xyxtrimyyx', 'xyz');

LTRIM

Removes specified characters from the left (beginning) of a string.

Syntax

LTRIM(<str> TEXT [, <characters> TEXT])

Parameters

Return type: TEXT

Example

-- Returns: test
SELECT LTRIM('zzzytest', 'xyz');

RTRIM

Removes specified characters from the right (end) of a string.

Syntax

RTRIM(<str> TEXT [, <characters> TEXT])

Parameters

Return type: TEXT

Example

-- Returns: test
SELECT RTRIM('testxxzx', 'xyz');

LPAD

Left-pads a string with a fill character to a specified total length.

Syntax

LPAD(<str> TEXT, <length> INT [, <fill> TEXT])

Parameters

Return type: TEXT

Example

-- Returns: xyxhi
SELECT LPAD('hi', 5, 'xy');

RPAD

Right-pads a string with a fill character to a specified total length.

Syntax

RPAD(<str> TEXT, <length> INT [, <fill> TEXT])

Parameters

Return type: TEXT

Example

-- Returns: hixyx
SELECT RPAD('hi', 5, 'xy');

ASCII

Returns the ASCII code of the first character of a string.

Syntax

ASCII(<str> TEXT)

Return type: INTEGER

Example

-- Returns: 120
SELECT ASCII('x');

CHAR_LENGTH

Returns the number of characters in a string. Equivalent to LENGTH.

Syntax

CHAR_LENGTH(<str> TEXT)

Return type: INTEGER

Example

-- Returns: 4
SELECT CHAR_LENGTH('jose');

LENGTH

Returns the number of characters in a string. In UTF-8 encoding, each Chinese character, digit, or letter counts as one character. Equivalent to CHAR_LENGTH.

Syntax

LENGTH(<str> TEXT)

Return type: INTEGER

Example

-- Returns: 4
SELECT LENGTH('jose');

CHR

Returns the character corresponding to a given ASCII or Unicode code value.

Syntax

CHR(<num> INT)

Return type: TEXT

Example

-- Returns: A
SELECT CHR(65);

INITCAP

Converts the first letter of each word to uppercase and the rest to lowercase. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.

Syntax

INITCAP(<str> TEXT)

Return type: TEXT

Example

-- Returns: Hi Thomas
SELECT INITCAP('hi THOMAS');

LOWER

Converts a string to lowercase.

Syntax

LOWER(<str> TEXT)

Return type: TEXT

Example

-- Returns: tom
SELECT LOWER('TOM');

UPPER

Converts a string to uppercase.

Syntax

UPPER(<str> TEXT)

Return type: TEXT

Example

-- Returns: TOM
SELECT UPPER('tom');

OCTET_LENGTH

Returns the number of bytes in a string.

Syntax

OCTET_LENGTH(<str> TEXT)

Return type: INTEGER

Example

-- Returns: 4
SELECT OCTET_LENGTH('jose');

MD5

Calculates the MD5 hash of a string and returns the result as a hexadecimal string.

Syntax

MD5(<str> TEXT)

Return type: TEXT

Example

-- Returns: 900150983cd24fb0d6963f7d28e17f72
SELECT MD5('abc');

POSITION

Returns the starting position of a substring within a string. Returns 0 if the substring is not found. Equivalent to STRPOS.

Syntax

POSITION(<substr> TEXT IN <str> TEXT)

Parameters

Return type: INTEGER

Example

-- Returns: 3
SELECT POSITION('om' IN 'Thomas');

STRPOS

Returns the starting position of a substring within a string. Returns 0 if the substring is not found. Equivalent to POSITION.

Syntax

STRPOS(<str> TEXT, <substr> TEXT)

Parameters

Return type: INTEGER

Example

-- Returns: 2
SELECT STRPOS('high', 'ig');

INSTR

Returns the position of a substring within a target string, with optional start position and occurrence control. Returns 0 if the substring is not found. This is an Oracle-compatible function that requires the orafce extension. For setup instructions, see Oracle-compatible functions.

Syntax

INSTR(<str> TEXT, <patt> TEXT [, <start> INTEGER [, <nth> INTEGER]])

Parameters

Return type: INTEGER

Examples

-- Returns: 4 (second occurrence of 'o', searching from position 1)
SELECT INSTR('Hologres', 'o', 1, 2);

-- Returns: 2 (first occurrence of 'o', searching from position 1)
SELECT INSTR('Hologres', 'o', 1, 1);

-- Returns: 4 (first occurrence of 'o' at or after position 4)
SELECT INSTR('Hologres', 'o', 4);

-- Returns: 2 (first occurrence of 'o')
SELECT INSTR('Hologres', 'o');

REPLACE

Replaces all occurrences of a substring within a string.

Syntax

REPLACE(<str> TEXT, <old_str> TEXT, <new_str> TEXT)

Parameters

Return type: TEXT

Example

CREATE TABLE animal_test (
    animal text,
    color text
);

INSERT INTO animal_test
    VALUES ('dog', 'white'), ('cat', 'white'), ('tiger', 'yellow');

SELECT animal, REPLACE(color, 'white', 'multi-colored') FROM animal_test;

Result:

animal | replace
-------+--------------
dog    | multi-colored
cat    | multi-colored
tiger  | yellow

REGEXP_REPLACE

Replaces substrings matching a POSIX regular expression with a replacement string.

Syntax

REGEXP_REPLACE(<str> TEXT, <regex> TEXT, <replacement> TEXT [, <flags> TEXT])

Parameters

Required:

Optional:

flags: One or more characters that control matching behavior. Default: replaces only the first match, case-sensitive.

Return type: TEXT

Example

CREATE TABLE a_test (
    a text
);

INSERT INTO a_test VALUES ('Abcd1234abCd');

SELECT REGEXP_REPLACE(a, '1234.', '77', 'ig') FROM a_test;

Result:

regexp_replace
--------------
Abcd77bCd

TRANSLATE

Replaces individual characters in a string based on a character mapping.

Syntax

TRANSLATE(<str> TEXT, <substr> TEXT, <newstr> TEXT)

Parameters

Return type: TEXT

Example

-- Returns: a2x5
SELECT TRANSLATE('12345', '143', 'ax');

REPEAT

Repeats a string a specified number of times.

Syntax

REPEAT(<str> TEXT, <number> INT)

Parameters

Return type: TEXT

Example

-- Returns: PgPgPgPg
SELECT REPEAT('Pg', 4);

STARTS_WITH

Returns true if a string starts with a specified prefix.

Syntax

STARTS_WITH(<str> TEXT, <prefix> TEXT)

Parameters

Return type: BOOLEAN (t for true, f for false)

Example

-- Returns: t
SELECT STARTS_WITH('alphabet', 'alph');

PARSE_IDENT

Parses a qualified SQL identifier string and returns its components as an array. Unquoted components are lowercased; quoted components preserve their case.

Syntax

PARSE_IDENT(<quali_iden> TEXT)

Parameters

Return type: TEXT (array format)

Example

-- Returns: {"SomeSchema","sometable"}
SELECT PARSE_IDENT('"SomeSchema".someTable');

QUOTE_IDENT

Returns the string quoted as a valid SQL identifier, adding double quotes when necessary (for example, when the string contains spaces or uppercase letters that would otherwise be lowercased).

Syntax

QUOTE_IDENT(<str> TEXT)

Return type: TEXT

Example

-- Returns: "Foo bar"
SELECT QUOTE_IDENT('Foo bar');

QUOTE_LITERAL

Returns the string quoted as a valid SQL string constant, escaping single quotes as needed.

Syntax

QUOTE_LITERAL(<str> TEXT)

Return type: TEXT

Example

-- Returns: 'O''Reilly'
SELECT QUOTE_LITERAL(E'O\'Reilly');

TO_HEX

Converts an integer to its hexadecimal string representation.

Syntax

TO_HEX(<number> INT | BIGINT)

Return type: TEXT

Example

-- Returns: 7fffffff
SELECT TO_HEX(2147483647);

TO_NUMBER

Converts a formatted string to a numeric value using a format model.

Syntax

TO_NUMBER(<str1> TEXT, <str2> TEXT)

Parameters

Return type: NUMERIC

Example

-- Returns: -12454.8
SELECT TO_NUMBER('12,454.8-', '99G999D9S');

PLVSTR.RVRS

Reverses part or all of a string. This is an Oracle-compatible function that requires the orafce extension. For setup instructions, see Oracle-compatible functions.

Syntax

Reverse the entire string:

PLVSTR.RVRS(<str> TEXT)

Reverse from a starting position to the end:

PLVSTR.RVRS(<str> TEXT, <start> INTEGER)

Reverse a specified range:

PLVSTR.RVRS(<str> TEXT, <start> INTEGER, <end> INTEGER)

Parameters

Return type: TEXT

Examples

-- Returns: sergoloH (entire string reversed)
SELECT PLVSTR.RVRS('Hologres');

-- Returns: sergo (reversed from position 4 to end)
SELECT PLVSTR.RVRS('Hologres', 4);

-- Returns: rg (positions 5-6 reversed)
SELECT PLVSTR.RVRS('Hologres', 5, 6);

ROW

Returns the row number of the current row. Supported in Hologres V1.3 and later.

To upgrade your instance to V1.3 or later, see Upgrade an instance. For support, join the Hologres DingTalk group. For instructions, see How do I get more online support?.

Syntax

ROW(<val1>, <val2>, ...)

Return type: ARRAY or TEXT

Example

The following example returns rows where column a and column b have equal values.

CREATE TABLE row_test (
    a text,
    b text
);

INSERT INTO row_test VALUES ('0', '0'), ('1', '0');

SELECT a, b FROM row_test WHERE ROW(a, b) = ROW(b, a);

Result:

a | b
--+--
0 | 0