String functions

The following table describes the common string functions supported by Hologres. The functions supported by Hologres are only a subset of the PostgreSQL functions. For more information about how to use these functions, see String Functions and Operators in the PostgreSQL documentation.

Function	Description	Example	Result
string || string	Concatenates two strings.	'Holo' || 'greSQL'	HologreSQL
bit_length(string)	Returns the length of a string as the number of bits.	bit_length('jose')	32
char_length(string)	Returns the length of a string as the number of characters.	char_length('jose')	4
lower(string)	Converts a string to lowercase.	lower('TOM')	tom
octet_length(string)	Returns the length of a string as the number of octets.	octet_length('jose')	4
position(substring in string)	Returns the location of a substring in a string.	position('om' in 'Thomas')	3
substring(string [from int] [for int])	Returns the characters extracted from a string based on the specified character position for a specified number of characters.	substring('Thomas' from 2 for 3)	hom
substring(string from pattern)	Returns a substring that matches a Portable Operating System Interface (POSIX) regular expression in a string.	substring('Thomas' from '... $')	mas
substring(string from pattern for escape)	Returns a substring that matches an SQL regular expression in a string.	substring('Thomas' from '%#"o_a#"_' for '#')	oma
trim([leading | trailing | both] [characters] from string)	Removes the longest string that contains specific characters from the start, end, or both sides of a string. Note If you do not specify the trimming type, the function removes the longest string that contains specific characters from both sides of the string. If you do not specify the trimming characters, the function removes spaces from specific parts of the string.	trim(both 'xyz' from 'yxTomxx')	Tom
trim([lea| tra| both] [from] string [, char] )	Removes the longest string that contains specific characters from the start, end, or both sides of a string. Note If you do not specify the trimming type, the function removes the longest string that contains specific characters from both sides of the string. If you do not specify the trimming characters, the function removes spaces from specific parts of the string.	trim(both from 'yxTomxx', 'xyz')	Tom
upper(string)	Converts a string to uppercase.	upper('tom')	TOM

The following table describes the additional string functions supported by Hologres. The functions supported by Hologres are only a subset of the PostgreSQL functions. For more information about how to use these functions, see String Functions and Operators in the PostgreSQL documentation.

Function	Description	Example	Result
ascii(string)	Returns the ASCII value of the first character in a string.	ascii('x')	120
btrim(string text [, characters text])	Removes the longest string that contains specific characters from both sides of a string. Note If you do not specify the trimming characters, the function removes spaces from both sides of the string.	btrim('xyxtrimyyx', 'xyz')	trim
chr(int)	Returns the character that matches an encoded value. Note The value must be valid and encoded in ASCII or UTF-8. In addition, the value cannot be 0.	chr(65)	A
concat(str "any" [, str "any" [, ...] ])	Concatenates all strings. This function ignores strings with NULL values.	concat('abcde', 2, NULL, 22)	abcde222
concat_ws(sep text, str "any" [, str "any" [, ...] ])	Concatenates strings by using a delimiter. Note The first argument specifies the delimiter and is not concatenated. This function ignores strings with NULL values.	concat_ws(',', 'abcde', 2, NULL, 22)	abcde,2,22
initcap(string)	Converts the first letter of each word in a string to uppercase and the rest to lowercase. Note A word consists of letters and digits. Make sure that words are separated with non-alphanumeric characters.	initcap('hi THOMAS')	Hi Thomas
length(string)	Returns the length of a string as the number of characters.	length('jose')	4
lpad(string text, length int [, fill text])	Prepends characters to the start of a string based on a specified length. Note If the string is longer than the specified length, it is truncated from the end. If the characters to be prepended to the start of the string are not specified, spaces are used.	lpad('hi', 5, 'xy')	xyxhi
ltrim(string text [, characters text])	Removes the longest string that contains specific characters from the start of a string. Note If you do not specify the trimming characters, the function removes spaces from the start of the string.	ltrim('zzzytest', 'xyz')	test
md5(string)	Calculates the MD5 hash of a string. The MD5 hash is a 32-character hexadecimal number.	md5('abc')	900150983cd24fb0 d6963f7d28e17f72
parse_ident(quali_iden text [,...] )	Parses a string.	parse_ident('"SomeSchema".someTable')	{SomeSchema,sometable}
quote_ident(string text)	Returns a string as a double quoted string so that it can be used as an identifier in an SQL statement. Note This function adds double quotation marks (") when the string contains non-identifier characters or would be case folded.	quote_ident('Foo bar')	"Foo bar"
quote_literal(string text)	Returns a string as a quoted string so that it can be used as a string literal in an SQL statement.	quote_literal(E'O\'Reilly')	'O''Reilly'
regexp_matches(string text, pattern text )	Returns all substrings that match a POSIX regular expression in a string.	regexp_match('foobarbequebaz', '(bar)(beque)')	{bar,beque}
regexp_replace(str text, pat text, replace text )	Replaces all substrings that match a POSIX regular expression in a string with a specified string.	regexp_replace('Thomas', '.[mN]a.', 'M')	ThM
regexp_split_to_array(string text, pattern text )	Splits a string by using a POSIX regular expression as the delimiter and returns its parts in an array.	regexp_split_to_array('hello world', '\s+')	{hello,world}
regexp_split_to_table(string text, pattern text )	Splits a string by using a POSIX regular expression as the delimiter and returns its parts in the rows of a table.	regexp_split_to_table('hello world', '\s+')	helloworld(2 rows)
repeat(string text, number int)	Repeats a string for a specified number of times.	repeat('Pg', 4)	PgPgPgPg
replace(string text, from text, to text)	Replaces all occurrences of a set of characters in a string with other specified characters.	replace('abcdefabcdef', 'cd', 'XX')	abXXefabXXef
rpad(string text, length int [, fill text])	Appends characters to the end of a string based on a specified length. Note If the string is longer than the specified length, it is truncated from the start. If the characters to be appended to the end of the string are not specified, spaces are used.	rpad('hi', 5, 'xy')	hixyx
rtrim(string text [, characters text])	Removes the longest string that contains specific characters from the end of a string. Note If you do not specify the trimming characters, the function removes spaces from the end of the string.	rtrim('testxxzx', 'xyz')	test
strpos(string, substring)	Returns the position of a substring in a string.	strpos('high', 'ig')	2
substr(string, from [, count])	Returns the characters extracted from a string based on the specified character position for a specified number of characters.	substr('alphabet', 3, 2)	ph
starts_with(string, prefix)	Checks whether a string starts with a specified prefix. This function returns true if the string starts with the prefix.	starts_with('alphabet', 'alph')	t
to_hex(number int or bigint)	Converts a number to its equivalent hexadecimal value.	to_hex(2147483647)	7fffffff
translate(string text, from text, to text)	Replaces all occurrences of specified characters with specified substitutes.	translate('12345', '143', 'ax')	a2x5