AnalyticDB:String functions and operators

Function

Data type of the return value

Description

Example

Sample result

string||string

text

Concatenates two strings.

'Post' || 'greSQL'

PostgreSQL

string||non-stringornon-string||string

text

Concatenates a string and a non-string.

'Value: ' || 42

Value: 42

bit_length()

int

Returns the number of bits in a string.

bit_length('jose')

32

char_length(string) or character_length()

int

Returns the number of characters in a string.

char_length('jose')

4

lower(string)

text

Converts uppercase letters in a string into lowercase letters.

lower('TOM')

tom

octet_length(string)

int

Returns the length of a string in bytes.

octet_length('jose')

4

overlay(placingfrom[for])

text

Replaces a substring at a specific position in a string.

overlay('Txxxxas' placing 'hom' from 2 for 4)

Thomas

position(in)

int

Finds the start position of a substring in a string.

position('om' in 'Thomas')

3

substring([from] [for])

text

Extracts a substring from a string.

substring('Thomas' from 2 for 3)

hom

substring(from)

text

Extracts a substring from a string based on a Portable Operating System Interface (POSIX) regular expression. For example, you can specify the start position and length for the extraction.

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

mas

substring(fromfor)

text

Extracts a substring from a string based on an SQL regular expression.

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

oma

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

text

Removes the longest substring or substrings that contain the specified characters from the start, the end, or both the start and the end of a string.

trim(both 'xyz' from 'yxTomxx')

Tom

trim([leading | trailing | both] [from][,] )

text

Removes the longest substring or substrings that contain spaces or the specified characters from the start, the end, or both the start and the end of a string.

trim(both from 'xTomxx', 'x')

Tom

upper()

text

Converts lowercase letters in a string into uppercase letters.

upper('tom')

TOM

Function

Data type of the return value

Description

Example

Sample result

ascii()

int

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

ascii('x')

120

btrim([,])

text

Removes the longest substrings that contain characters in a specific character set from the start and the end of a string. If no character set is specified, whitespace characters such as spaces and tabs are removed by default.

btrim('xyxtrimyyx', 'xyz')

trim

chr(

text

Converts an integer that represents an ASCII or extended ASCII code value into the corresponding character.

chr(65)

A

concat([,[, ...] ])

text

Concatenates two or more strings into a string.

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

abcde222

concat_ws(,[,[, ...] ])

text

Concatenates multiple strings into a string and inserts a delimiter that you specify at the beginning of the variables of the function between the concatenated strings.

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

abcde,2,22

convert(,,)

bytea

Converts a string from the source encoding format into the destination encoding format.

convert('text_in_utf8', 'UTF8', 'LATIN1')

text_in_utf8 in the Latin-1 (ISO 8859-1) encoding format

convert_from(,)

text

Converts a binary string of the BYTEA type into a string of the TEXT type by using the specified source encoding format.

convert_from('text_in_utf8', 'UTF8')

text_in_utf8 in the current database encoding format

convert_to(,)

bytea

Converts a string of the TEXT type into a binary string of the BYTEA type by using the specified destination encoding format.

convert_to('some text', 'UTF8')

some text in the UTF-8 encoding format

decode(,)

bytea

Decodes a hexadecimal string of the TEXT type into the binary representation of the BYTEA type.

decode('MTIzAAE=', 'base64')

\x3132330001

encode(,)

text

Encodes a binary string of the BYTEA type into a string of the TEXT type in a specific format.

encode('123\000\001', 'base64')

MTIzAAE=

format([,[, ...] ])

text

Outputs a formatted string.

format('Hello %s, %1$s', 'World')

Hello World, World

initcap()

text

Converts the first letter of each word in a string into the corresponding uppercase letter and the other letters into the corresponding lowercase letters.

initcap('hi THOMAS')

Hi Thomas

left(,)

text

Returns the first n characters of a string.

left('abcde', 2)

ab

length()

int

Counts the number of characters in a string, including spaces and special characters.

length('jose')

4

length(,)

int

Counts the number of characters in a string in a specific encoding format.

length('jose', 'UTF8')

4

lpad(,[,])

text

Pads a string on the left to a specific length with a sequence of specified characters.

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

xyxhi

ltrim([,])

text

Removes the characters in a specific character set from the left side of a string. If no character set is specified, whitespace characters such as spaces, tabs, and line feeds are removed by default.

ltrim('zzzytest', 'xyz')

test

md5()

text

Calculates the MD5 hash value of a string.

md5('abc')

900150983cd24fb0 d6963f7d28e17f72

parse_ident([,DEFAULT true ] )

text[]

Parses a string that conforms to SQL identifier rules and splits the string into a schema and an object name.

parse_ident('"SomeSchema".someTable')

{SomeSchema,sometable}

pg_client_encoding()

name

Returns the client encoding name of the current session.

pg_client_encoding()

SQL_ASCII

quote_ident()

text

Converts a string into an identifier that can be safely used in an SQL statement.

quote_ident('Foo bar')

"Foo bar"

quote_literal()

text

Forcibly converts a string into the TEXT type and encloses the string in quotation marks.

quote_literal(E'O\'Reilly')

'O''Reilly'

quote_literal(valueanyelement)

quote_literal()

text

Forcibly converts a value into the TEXT type and encloses the value in quotation marks.

quote_literal(42.5)

'42.5'

quote_nullable()

text

Forcibly converts a string into the TEXT type and encloses the string in quotation marks. If you specify NULL in the function, NULL is returned.

quote_nullable(NULL)

NULL

quote_nullable()

text

Forcibly converts a value of any type into the TEXT type and encloses the value in quotation marks.

quote_nullable(42.5)

'42.5'

regexp_match(,[,])

text[]

Performs a regular expression match. This function searches for substrings that match a specific pattern in a specific string and returns an array of the matches. If multiple matches exist, each match is returned as an element. If no matches exist, NULL is returned.

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

{bar,beque}

regexp_matches(,[,])

setof text[]

Performs a regular expression match. This function searches for substrings that match a specific pattern in a specific string and returns all matches.

regexp_matches('foobarbequebaz', 'ba.', 'g')

{bar}

{baz}

(2 rows)

regexp_replace(,,[,])

text

Replaces substrings that match a POSIX regular expression.

regexp_replace('Thomas', '.[mN]a.', 'M')

ThM

regexp_split_to_array(,[,])

text[]

Uses a POSIX regular expression as the delimiter to split a string.

regexp_split_to_array('hello world', '\s+')

{hello,world}

regexp_split_to_table(,[,])

setof text

Uses a POSIX regular expression as the delimiter to split a string.

regexp_split_to_table('hello world', '\s+')

hello

world

(2 rows)

repeat(,))

text

Repeats a specific string a specific number of times.

repeat('Pg', 4)

PgPgPgPg

replace(,,)

text

Finds a specific substring in a specific string and replaces the substring with another specified substring.

replace('abcdefabcdef', 'cd', 'XX')

abXXefabXXef

reverse()

text

Reverses the order of the characters in a specific string.

reverse('abcde')

edcba

right(,)

text

Extracts the rightmost n characters from a specific string.

right('abcde', 2)

de

rpad(,[,])

text

Pads a string on the right to a specific length with a sequence of specified characters.

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

hixyx

rtrim([,])

text

Removes specified characters or whitespace characters from the end of a string.

rtrim('testxxzx', 'xyz')

test

split_part(,,)

text

Splits a string by using a specific delimiter.

split_part('abc~@~def~@~ghi', '~@~', 2)

def

strpos(,)

int

Finds the position of the first occurrence of a substring in a string.

strpos('high', 'ig')

2

substr(,[,])

text

Extracts a substring from a string.

substr('alphabet', 3, 2)

ph

starts_with(,)

bool

Returns t, which indicates true, if a specific string starts with a specific prefix.

starts_with('alphabet', 'alph')

t

to_ascii([,])

text

Converts a string from another encoding format into ASCII. Only LATIN1, LATIN2, LATIN9, and WIN1250 encoding formats can be converted.

to_ascii('Karel')

Karel

to_hex(or)

text

Converts a numeric value of the INTEGER or BIGINT type into the corresponding hexadecimal representation.

to_hex(2147483647)

7fffffff

translate(,,)

text

Replaces specific characters in a string with other characters.

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

a2x5