Hologres:String functions

Description: Concatenates two strings.

<string_value1> TEXT || <string_value2> TEXT

Parameters

string_value1 and string_value2: the string expressions that you want to concatenate.

Return value

A value of the TEXT type is returned.

Example

-- Returned result: HologreSQL.
SELECT 'Holo' || 'greSQL';

Description: Concatenates two or more string values and returns a continuous string.

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

Parameters

str1,...,strN: the strings that you want to concatenate.

Return value

A value of the TEXT type is returned.

Example

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

The following result is returned:

concat
---------
abcde222

Description: Uses the delimiter specified by the first parameter to concatenate the strings specified by other parameters.

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

Parameters

• sep: required. The delimiter that is used to concatenate the strings.

  Note

  If you set this parameter to NULL, this function returns NULL.

• str: required. The string that you want to concatenate.

  Note

  Strings with the NULL value are ignored.

Return value

A value of the TEXT type is returned.

Example

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

The following result is returned:

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

Description: Aggregates values of a column in multiple rows into a string. This function is compatible with Oracle. Before you use this function, you must install the Orafce extension. For more information, see Supported Oracle functions.

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

Parameters

• str1: required. The string expression or column whose values you want to aggregate.

• str2: optional. The delimiter.

Return value

A value of the TEXT type is returned.

Examples

• Example 1

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

• Example 2

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

Description: Extracts a substring that matches specific rules from a string.

• Extract a substring at a specified position from a string.

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

• Extract a substring that matches a POSIX regular expression from a string.

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

• Extract a substring that matches an SQL regular expression from a string.

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

Parameters

• str: required. The string from which you want to extract a substring.

• num_start and num_end: optional. The start position and end position.

• pattern: required. The regular expression based on which a substring is extracted.

• escape: required. The escape character.

  Note

  In regular expressions, certain characters such as the period (.), asterisk (*), and plus sign (+) have special meanings. If you want to match these characters rather than their special meanings, you must add an escape character before them.

Return value

A value of the TEXT type is returned.

Examples

• Example 1: Extract a substring at a specified position from a string.

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

  The following result is returned:

  substring
  ----------
  hom

• Example 2: Extract a substring that matches a POSIX regular expression from a string.

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

  The following result is returned:

  substring
  ----------
  mas

• Example 3: Extract a substring that matches an SQL regular expression from a string.

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

  The following result is returned:

  substring
  ----------
  oma

Description: Matches the content of a string by using regular expressions. Content fragments that meet the matching conditions are displayed in an array.

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

Parameters

• str: required. The string to be matched.

• pattern: required. The regular expression.

Return value

A value of the ARRAY type is returned.

Example

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

The following result is returned:

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

Description

• Returns a substring that is extracted starting from the specified position to the end of a string. This function is compatible with Oracle. Before you use this function, you must install the Orafce extension. For more information, see Supported Oracle functions.

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

  Parameters

  • str: required. The string from which you want to extract a substring.

  • num_start: the position starting from which you want to extract a substring.

• Returns a substring of the specified length that is extracted starting from the specified position. This function is compatible with Oracle. Before you use this function, you must install the Orafce extension. For more information, see Supported Oracle functions.

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

  Parameters

  • str: required. The string from which you want to extract a substring.

  • num_start: the position starting from which you want to extract a substring.

  • len: the length of the substring that you want to extract.

Return value

A value of the TEXT type is returned.

Examples

• Example 1: Extract a substring starting from the fourth character to the end of a string.

  -- Returned result: ogres.
  SELECT SUBSTR('Hologres', 4);

• Example 2: Extract a five-character substring starting from the second character of a string.

  -- Returned result: ologr.
  SELECT SUBSTR('Hologres', 2, 5);

Description: Splits a string based on a specific delimiter and returns the Nth segment.

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

Parameters

• str: required. The string that you want to split.

• delimiter: required. The delimiter that you want to use to split the string.

• num: required. The sequence number of the split segment that you want to return. The value must be an integer and starts from 1.

Return value

A value of the TEXT type is returned. If the value of num exceeds the number of split segments, this 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;

The following result is returned:

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

Description: Aggregates values of a column in multiple rows into a string.

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

Usage notes: Only Hologres V1.3 and later support filter conditions.

Parameters

• expression: required. The expression or column whose values you want to aggregate.

• order_by_clause: optional. The order based on which you want to sort expression values before aggregating the values into a string. If you do not specify this parameter, data in the database may be sorted in an appropriate way.

• FILTER ( WHERE filter_clause ): optional. The condition used to filter the rows whose values you want to aggregate.

Return value

A value of the TEXT type is returned.

Examples

• Example 1

  CREATE TABLE city_test (
      country text,
      city text
  );
  
  INSERT INTO city_test
      VALUES ('China', 'Shanghai'), ('China', 'Taipei'), ('Japan', 'Tokyo'), ('France', 'Paris'), ('Britain', 'London');
  
  SELECT
      STRING_AGG(city, ',')
  FROM
      city_test;

  The following result is returned:

  string_agg
  ------------------------
  Shanghai,Taipei,Tokyo,Paris,London

• Example 2

  CREATE TABLE name_text (
      student_id int,
      name text);
  INSERT INTO name_text
      VALUES (
          1, 'Jack'), (
          2, 'Mike'), (
          3, 'John'), (
          4, 'Saturday'
  );
  
  SELECT
      STRING_AGG(name, ',') FILTER (WHERE student_id > 2)
  FROM
      name_text;

  The following result is returned:

  string_agg
  ----------
  John,Saturday

Description: Extracts the specified number of characters starting from the start of a string.

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

Parameters

• str: required. The string from which you want to extract a substring.

• num: required. The number of characters that you want to extract starting from the start of a string. The value must be an integer.

  If num is set to a negative value, characters excluding the last ‒num characters are returned.

Return value

A value of the TEXT type is returned.

Example

SELECT LEFT('hologres', 4);

The following result is returned:

left
----
holo

Description: Extracts the specified number of characters starting from the end of a string.

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

Parameters

• str: required. The string from which you want to extract a substring.

• num: required. The number of characters that you want to extract starting from the end of a string. The value must be an integer.

  If num is set to a negative value, characters excluding the first ‒num characters are returned.

Return value

A value of the TEXT type is returned.

Example

SELECT RIGHT('hologres', 4);

The following result is returned:

right
----------
gres

Description: Removes specific characters from the start, end, or both sides of a string.

TRIM([leading | trailing | both] [<characters> TEXT] FROM STRING)

Parameters

• Parameters that specify the position to trim:

  • leading: optional. Specific characters are removed from the start (left side) of a string.

  • trailing: optional. Specific characters are removed from the end (right side) of a string.

  • both: optional. Specific characters are removed from both sides of a string. This is the default value.

• characters: optional. The set of characters that you want to remove from a string.

• string: required. The string from which you want to remove specific characters.

Return value

A value of the TEXT type is returned.

Example

-- Returned result: Tom.
select TRIM(BOTH 'xyz' FROM 'yxTomxx');

Description: Removes specific characters from both sides of a string.

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

Parameters

• str: required. The string from which you want to remove specific characters.

• characters: optional. The set of characters that you want to remove from both sides of a string.

  Note

  Characters specified in this parameter are matched independently, not as a whole.

  If you do not specify the characters parameter, a space is used by default.

Return value

A value of the TEXT type is returned.

Example

-- Returned result: trim.
SELECT BTRIM('xyxtrimyyx', 'xyz');

Description: Removes specific characters from the left side (start) of a string.

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

Parameters

• str: required. The string from which you want to remove specific characters.

• characters: optional. The set of characters that you want to remove from the left side (start) of a string.

  Note

  • Characters specified in this parameter are matched independently, not as a whole.

  • If you do not specify the characters parameter, a space is used by default.

Return value

A value of the TEXT type is returned.

Example

-- Returned result: test.
SELECT LTRIM('zzzytest', 'xyz');

Description: Removes specific characters from the right side (end) of a string.

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

Parameters

• str: required. The string from which you want to remove specific characters.

• characters: optional. The set of characters that you want to remove from the right side (end) of a string. If you do not specify the characters parameter, a space is used by default.

  Note

  Characters specified in this parameter are matched independently, not as a whole.

Return value

A value of the TEXT type is returned.

Example

-- Returned result: test.
SELECT RTRIM('testxxzx', 'xyz');

Description: Pads specific characters to the left side of a string to reach a specified length.

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

Parameters

• str: required. The string to which you want to pad specific characters.

• length: required. The length of the string that you want to return.

  Note

  If the length of the string to which you want to pad specific characters exceeds the value of this parameter, this function truncates the string from the right side to the specified length.

• fill: optional. The characters that you want to pad. If you do not specify the fill parameter, a space is used by default.

Return value

A value of the TEXT type is returned.

Example

-- Returned result: xyxhi.
SELECT LPAD('hi', 5, 'xy');

Description: Pads specific characters to the right side of a string to reach a specified length.

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

Parameters

• str: required. The string to which you want to pad specific characters.

• length: required. The length of the string that you want to return.

  Note

  If the length of the string to which you want to pad specific characters exceeds the value of this parameter, this function truncates the string from the right side to the specified length.

• fill: optional. The characters that you want to pad. If you do not specify the fill parameter, a space is used by default.

Return value

A value of the TEXT type is returned.

Example

-- Returned result: hixyx.
SELECT RPAD('hi', 5, 'xy');

Description: Converts a string into a number.

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

Parameters

• str1: the string that you want to convert.

• str2: the format model, which is used to parse the string that you want to convert.

Return value

A value of the NUMERIC type is returned.

Example

-- Returned result: -12454.8.
SELECT TO_NUMBER('12,454.8-', '99G999D9S');

Description: Replaces a substring in a string with another substring.

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

Parameters

• str: required. The string whose substring you want to replace.

• old_str: required. The substring that you want to replace.

• new_str: required. The substring that you want to use to replace old_str.

Return value

A value of the TEXT type is returned.

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', 'multicolored')
FROM
    animal_test;

The following result is returned:

animal |  replace   
-------------------
dog | multicolored
cat | multicolored
tiger | yellow

Description: Replaces a substring with another substring by using a POSIX regular expression.

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

Parameters

• str: required. The string whose substring you want to replace.

• regex: required. The regular expression.

• replacement: required. The substring that you want to use to replace the matched substring.

• flags: optional. The flags that indicate the regular expression matches.

Return value

A value of the TEXT type is returned.

Example

CREATE TABLE a_test (
    a text
);
INSERT INTO a_test VALUES ('Abcd1234abCd');

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

The following result is returned:

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

Description: Reverses the order of a string. This function is compatible with Oracle. Before you use this function, you must install the Orafce extension. For more information, see Supported Oracle functions.

• Extract a substring from a string and reverse the order of the substring. The start and end positions of the substring in the string are specified.

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

• Extract a substring from a string and reverse the order of the substring. The substring is extracted starting from the specified position to the end of the string.

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

• Reverse the order of the entire string.

  PLVSTR.RVRS(<str> TEXT)

Return value

A value of the TEXT type is returned.

Examples

• Example 1: Extract a substring from a string and reverse the order of the substring. The start and end positions of the substring in the string are specified.

  -- Returned result: rg.
  SELECT PLVSTR.RVRS('Hologres', 5,6);

• Example 2: Extract a substring from a string and reverse the order of the substring. The substring is extracted starting from the specified position to the end of the string.

  -- Returned result: sergo.
  SELECT PLVSTR.RVRS('Hologres', 4);

• Example 3: Reverse the order of the entire string.

  -- Returned result: sergoloH.
  SELECT PLVSTR.RVRS('Hologres');

Description: Returns the length of a string calculated by the number of characters.

CHAR_LENGTH(<str> TEXT)

Return value

A value of the TEXT type is returned.

Example

-- Returned result: 4.
SELECT CHAR_LENGTH('jose');

Description: Returns the length of a string calculated by the number of bytes. When the UTF-8 character set is used to encode the string, one Chinese character occupies three bytes, and one digit or letter occupies one byte.

LENGTH(<str> TEXT)

Return value

A value of the TEXT type is returned.

Example

-- Returned result: 4.
SELECT LENGTH('jose');

Description: Converts a string into lowercase letters.

LOWER(<str> TEXT)

Return value

A value of the TEXT type is returned.

Example

-- Returned result: tom.
SELECT LOWER('TOM');

Description: Converts a string into uppercase letters.

UPPER(<str> TEXT)

Return value

A value of the TEXT type is returned.

Example

-- Returned result: TOM.
SELECT UPPER('tom');

Description: Converts the first letter of each word in a string into an uppercase letter and converts the other letters of each word into lowercase letters.

INITCAP(<str> TEXT)

Return value

A value of the TEXT type is returned.

Example

-- Returned result: Hi Thomas.
SELECT INITCAP('hi THOMAS');

Description: Returns the length of a string calculated by the number of bytes.

OCTET_LENGTH(<str> TEXT)

Return value

A value of the INT type is returned.

Example

-- Returned result: 4.
SELECT OCTET_LENGTH('jose');

Description: Returns the position of a substring in a string.

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

Parameters

• substr: required. The substring.

• str: The string.

Return value

A value of the INT type is returned.

Example

-- Returned result: 3.
SELECT POSITION('om' IN 'Thomas');

Description: Returns the position of a substring in a string.

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

Parameters

• str: required. The string.

• substr: required. The substring.

Return value

A value of the INT type is returned.

Example

-- Returned result: 2.
SELECT STRPOS('high', 'ig');

Description: Returns the position of a substring in a string based on a specific range. If the substring is unavailable, this function returns 0. This function is compatible with Oracle. Before you use this function, you must install the Orafce extension. For more information, see Supported Oracle functions.

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

Parameters

• str: required. The string.

• patt: required. The substring to be matched.

• start: optional. The start position from which the substring is matched. If you do not specify this parameter, the substring is matched from the start of the string.

• nth: optional. The position of the nth occurrence of the substring is returned. If you do not specify this parameter, this function returns the position of the first occurrence of the substring.

Return value

A value of the INT type is returned.

Examples

• Example 1

  -- Returned result: 4.
  SELECT INSTR('Hologres', 'o',1,2);

• Example 2

  -- Returned result: 2.
  SELECT INSTR('Hologres', 'o',1,1);

• Example 3

  -- Returned result: 4.
  SELECT INSTR('Hologres', 'o',4);

• Example 4

  -- Returned result: 2.
  SELECT INSTR('Hologres', 'o');

Description: Calculates the MD5 hash of a string. The MD5 hash is a hexadecimal number.

MD5(<str> TEXT)

Return value

A value of the TEXT type is returned.

Example

-- Returned result: 900150983cd24fb0d6963f7d28e17f72.
SELECT MD5('abc');

Description: Parses a string that follows the rules for SQL identifiers.

PARSE_IDENT(<quali_iden> TEXT [,...] )

Parameters

• parse_ident: required. The string to be parsed, which is a fully qualified identifier of a database object in most cases.

Return value

A value of the TEXT type is returned.

Example

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

Description: Returns a string as a quoted identifier in an SQL statement.

QUOTE_IDENT(<str> TEXT)

Return value

A value of the TEXT type is returned.

Example

-- Returned result: "Foo bar".
SELECT QUOTE_IDENT('Foo bar');

Description: Returns a string as a quoted string to be used as a string literal in an SQL statement.

QUOTE_LITERAL(<str> TEXT)

Return value

A value of the TEXT type is returned.

Example

-- Returned result: 'O''Reilly'.
SELECT QUOTE_LITERAL(E'O\'Reilly');

Description: Returns the ASCII value of the first character in a string.

ASCII(<str> TEXT)

Return value

A value of the TEXT type is returned.

Example

-- Returned result: 120.
SELECT ASCII('x');

Description: Returns the character that matches an encoded value.

CHR(<num> INT)

Return value

A value of the TEXT type is returned.

Example

-- Returned result: A.
SELECT CHR(65);

Description: Repeats a string a specific number of times.

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

Parameters

• str: required. The string.

• number: required. The number of times that you want to repeat the string.

Return value

A value of the TEXT type is returned.

Example

-- Returned result: PgPgPgPg.
SELECT REPEAT('Pg', 4);

Description: Checks whether a string starts with a specific prefix.

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

Parameters

• str: required. The string.

• prefix: required. The prefix.

Return value

A value of the BOOLEAN type is returned. If the string starts with the specified prefix, this function returns t, which indicates true. In other cases, this function returns f, which indicates false.

Example

-- Returned result: t.
SELECT STARTS_WITH('alphabet', 'alph');

Description: Splits a string and converts the split data into rows. This function can be used to transpose rows to columns.

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

Parameters

• str: required. The string to be split.

• pattern: required. The regular expression based on which the string is split. The string is split based on the special character and construct in the regular expression.

Return value

A value of the TEXT type is returned.

Example

CREATE TABLE interests_test (
    name text,
    intrests text
);

INSERT INTO interests_test
    VALUES ('Ava', 'singing, dancing'), ('Bob', 'playing football, running, painting'), ('Jack', 'arranging flowers, writing calligraphy, playing the piano, sleeping');

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

The following result is returned:

name | regexp_split_to_table
---------------------------
Ava | singing
Ava | dancing
Bob | playing football
Bob | running
Bob | painting
Jack | arranging flowers
Jack | writing calligraphy
Jack | playing the piano
Jack | sleeping

Description: Splits a string based on a regular expression and returns an array.

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

Parameters

• str: required. The string to be split.

• pattern: required. The regular expression based on which the string is split. The string is split based on the special character and construct in the regular expression.

Return value

A value of the ARRAY type is returned.

Example

CREATE TABLE interests_test (
    name text,
    intrests text
);

INSERT INTO interests_test
    VALUES ('Ava', 'singing, dancing'), ('Bob', 'playing football, running, painting'), ('Jack', 'arranging flowers, writing calligraphy, playing the piano, sleeping');

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

The following result is returned:

name | regexp_split_to_array
----------------------------
Ava | {singing, dancing}
Bob | {playing football, running, painting}
Jack | {arranging flowers, writing calligraphy, playing the piano, sleeping}

Description: Converts a number into its equivalent hexadecimal value.

TO_HEX(<number> INT OR BIGINT)

Return value

A value of the TEXT type is returned.

Example

-- Returned result: 7fffffff.
SELECT TO_HEX(2147483647);

Description: Replaces characters in a string with specified characters.

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

Parameters

• str: required. The string.

• substr: required. The substring that you want to replace.

• newstr: required. The substring that you want to use to replace substr.

Return value

A value of the TEXT type is returned.

Example

-- Returned result: a2x5.
SELECT TRANSLATE('12345', '143', 'ax');

Description: Returns the number of the row in which a formula is located.

ROW()

Usage note: This function is supported only in Hologres V1.3 and later.

Return value

A value of the ARRAY or TEXT type is returned.

Example

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);

The following result is returned:

a | b 
------
0 | 0