Hologres:String functions

Function

Return value type

Description

Example

Returned result

to_number(text, text)

NUMERIC

Converts a string into a number.

select to_number('12,454.8-', '99G999D9S');

-12454.8

string_agg(expression,delimiter)

TEXT

Concatenates the non-null values of the specified expression into a string by using a delimiter. This function can be used to transpose columns to rows.

create table city_test(
  country text,
  city text
);
insert into city_test values ('China', 'Shanghai'),('China', 'Taipei'),('Japan', 'Tokyo'),('France', 'Paris'),('UK', 'London');
select string_agg(city,',') from city_test;

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

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

TEXT

Concatenates the non-null values of an expression into a string by using a delimiter. This function can be used to transpose columns to rows. You can use a FILTER clause in the function expression.

create table name_text(student_id int,name text)
insert into name_text values (1, 'Ava'),(2, 'Bob'),(3, 'Jack'),(4, 'Alex');
select string_agg(name,',') filter (where student_id >2) from name_text ;

string_agg
----------
Jack,Alex

regexp_split_to_table(string text, pattern text)

TEXT

A string splitting function. This function transposes the split data to rows and can be used to transpose rows to columns.

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;

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

regexp_split_to_array(string text, pattern text)

ARRAY

A string splitting function. This function transposes the split data to arrays and can be used to transpose rows to arrays.

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;

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

replace(string text, old_text, new_text)

TEXT

Replaces a substring in a string with another substring.

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; 

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

regexp_replace(string, regex, replacement[, flags])

TEXT

Replaces a substring with another substring by using a Portable Operating System Interface (POSIX) regular expression.

create table a_test(a text);
insert into a_test values ('Abcd1234abCd');
SELECT regexp_replace(a, '1234.', '77', 'ig') from a_test;

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

regexp_match(string text, pattern text)

ARRAY

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

select regexp_match('foobarbequebaz', '(bar)(beque)')

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

row_to_json

JSON

Converts a row of data into a JSON object.

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 row_to_json(t) from (select name,intrests from interests_test) as t;

• The following result is returned for versions earlier than Hologres V1.3.52:

  row_to_json                
  ------------------------------
  {"f1":"Ava","f2":"singing, dancing"}
  {"f1":"Bob","f2":"playing football, running, painting"}
  {"f1":"Jack","f2":"arranging flowers, writing calligraphy, playing the piano, sleeping"} 

• The following result is returned for Hologres V1.3.52 and later:

  row_to_json                
  ------------------------------
  "{"name": "Jack", "interests": "arranging flowers, writing calligraphy, playing the piano, sleeping"}"
  "{"name": "Ava", "interests": "singing, dancing"}"
  "{"name": "Bob", "interests": "playing football, running, painting"}" 

row()

ARRAY/TEXT

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

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

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

Function

Return value type

Description

Example

Returned result

split_part(string text ,delimiter text,n int)

TEXT

Splits a string with a specific delimiter and returns the Nth string.

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;

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

concat(str "any" [, str "any" [, ...] ])

TEXT

Concatenates all strings. This function ignores strings with NULL values.

select concat('abcde', 2, NULL, 22);

concat
---------
abcde222

concat_ws(sep text, str "any" [, str "any" [, ...] ])

TEXT

Concatenates strings excluding the first string with a delimiter.

select concat_ws(',', 'abcde', 2, NULL, 22);

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

substring(string [from int] [for int])

TEXT

Returns a substring extracted from a string based on the specified character location for a specific number of characters.

select substring('Thomas' from 2 for 3);

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

substring(string from pattern)

TEXT

Returns a substring that matches a Portable Operating System Interface (POSIX) regular expression in a string.

select substring('Thomas' from '...$');

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

substring(string from pattern for escape)

TEXT

Returns a substring that matches an SQL regular expression in a string.

select substring('Thomas' from '%#"o_a#"_' for '#');

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

left(string text, n int)

TEXT

Returns the first n characters of a string. If n is set to a negative value, characters excluding the last ‒n characters are returned.

select left('hologres', 4);

left
----
holo

right(string text, n int)

TEXT

Returns the last n characters of a string. If n is set to a negative value, characters excluding the first ‒n characters are returned.

select right('hologres', 4);

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

string || string

TEXT

Concatenates two strings.

select 'Holo' || 'greSQL';

HologreSQL

bit_length(string)

TEXT

Returns the length of a string as the number of bits.

select bit_length('jose');

32

char_length(string)

TEXT

Returns the length of a string as the number of characters.

select char_length('jose');

4

length(string)

TEXT

Returns the length of a string as 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.

select length('jose');

4

lower(string)

TEXT

Converts a string into lowercase letters.

select lower('TOM');

tom

upper(string)

TEXT

Converts a string into uppercase letters.

select upper('tom');

TOM

initcap(string)

TEXT

Converts the first letter of each word in a string to an uppercase letter and converts the rest to lowercase letters.

select initcap('hi THOMAS');

Hi Thomas

octet_length(string)

TEXT

Returns the length of a string as the number of octets.

select octet_length('jose');

4

position(substring in string)

TEXT

Returns the location of a substring in a string.

select position('om' in 'Thomas');

3

strpos(string, substring)

TEXT

Returns the location of a substring in a string.

select strpos('high', 'ig');

2

trim([leading | trailing | both] [characters] from string)

TEXT

Removes the longest string that contains specific characters from the start, end, or both sides of a string.

select trim(both 'xyz' from 'yxTomxx');

Tom

btrim(string text [, characters text])

TEXT

Removes the longest string that contains specific characters from the start and the end of a string.

select btrim('xyxtrimyyx', 'xyz');

trim

ltrim(string text [, characters text])

TEXT

Removes the longest string that contains specific characters from the start of a string.

select ltrim('zzzytest', 'xyz');

test

rtrim(string text [, characters text])

TEXT

Removes the longest string that contains specific characters from the end of a string.

select rtrim('testxxzx', 'xyz');

test

lpad(string text, length int [, fill text])

TEXT

Prepends characters to the start of a string based on a specific length.

select lpad('hi', 5, 'xy');

xyxhi

rpad(string text, length int [, fill text])

TEXT

Prepends characters to the right side of a string based on a specific length.

select rpad('hi', 5, 'xy');

hixyx

md5(string)

TEXT

Calculates the MD5 hash of a string. The MD5 hash is a 32-character hexadecimal number.

select md5('abc');

900150983cd24fb0d6963f7d28e17f72

parse_ident(quali_iden text [,...] )

TEXT

Parses a string.

select parse_ident('"SomeSchema".someTable');

{SomeSchema,sometable}

quote_ident(string text)

TEXT

Returns a string as a double-quoted string to be used as an identifier in an SQL statement.

select quote_ident('Foo bar');

"Foo bar"

quote_literal(string text)

TEXT

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

select quote_literal(E'O\'Reilly');

'O''Reilly'

ascii(string)

TEXT

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

select ascii('x');

120

chr(int)

TEXT

Returns the character that matches an encoded value.

select chr(65);

A

repeat(string text, number int)

TEXT

Repeats a string for a specific number of times.

select repeat('Pg', 4);

PgPgPgPg

starts_with(string, prefix)

BOOLEAN

If the string starts with the specified prefix, the value t that indicates true is returned. Otherwise, the value f that indicates false is returned.

select starts_with('alphabet', 'alph');

t

to_hex(number int or bigint)

TEXT

Converts a number to its equivalent hexadecimal value.

select to_hex(2147483647);

7fffffff

translate(string text, from text, to text)

TEXT

Replaces all occurrences of the characters in the from set with the characters in the to set.

select translate('12345', '143', 'ax');

a2x5