String Functions and Operators

str1 || str2

Returns the concatnenated string of both side strings str1 and str2.

Parameters:
  • str1 – first string
  • str2 – second string
Return type:

text
Example: 
select 'Ta' || 'jo';
> 'Tajo'
ascii(string text)

Returns the ASCII code of the first character of the text. For UTF-8, this function returns the Unicode code point of the character. For other multibyte encodings, the argument must be an ASCII character.

Parameters:string – input string
Return type:int4
Example: 
select ascii('x');
> 120
bit_length(string text)

Returns the number of bits in string.

Parameters:string – input string
Return type:int4
Example: 
select bit_length('jose');
> 32
char_length(string text)

Returns the number of characters in string.

Parameters:string – to be counted
Return type:int4
Alias:character_length, length
Example: 
select char_length('Tajo');
> 4
octet_length(string text)

Returns the number of bytes in string.

Parameters:string – input string
Return type:int4
Example: 
select octet_length('jose');
> 4
chr(code int4)

Returns a character with the given code.

Parameters:code – input character code
Return type:char
Example: 
select chr(65);
> A
decode(binary text, format text)

Decode binary data from textual representation in string.

Parameters:
  • binary – encoded value
  • format – decode format. base64, hex, escape. escape converts zero bytes and high-bit-set bytes to octal sequences (nnn) and doubles backslashes.
Return type:

text
Example: 
select decode('MTIzXDAwMFwwMDE=', 'base64');
> 123\\000\\001
digest(input text, method text)

Calculates the Digest hash of string.

Parameters:
  • input – input string
  • method – hash method
Return type:

text
Example: 
select digest('tajo', 'sha1');
> 02b0e20540b89f0b735092bbac8093eb2e3804cf
encode(binary text, format text)

Encode binary data into a textual representation.

Parameters:
  • binary – decoded value
  • format – encode format. base64, hex, escape. escape converts zero bytes and high-bit-set bytes to octal sequences (nnn) and doubles backslashes.
Return type:

text
Example: 
select encode('123\\000\\001', 'base64');
> MTIzXDAwMFwwMDE=
initcap(string text)

Convert the first letter of each word to upper case and the rest to lower case.

Parameters:string – input string
Return type:text
Example: 
select initcap('hi THOMAS');
> Hi Thomas
md5(string text)

Calculates the MD5 hash of string.

Parameters:string – input string
Return type:text
Example: 
select md5('abc');
> 900150983cd24fb0d6963f7d28e17f72
left(string text, number int4)

Returns the first n characters in the string.

Parameters:
  • string – input string
  • number – number of characters retrieved
Return type:

text
Example: 
select left('ABC', 2);
> AB

Returns the last n characters in the string.

Parameters:
  • string – input string
  • number – number of characters retrieved
Return type:

text
Example: 
select right('ABC', 2);
> BC
locate(source text, target text[, start_index int4])

Returns the location of specified substring.

Parameters:
  • source – source string
  • target – target substring
  • start_index – the index where the search is started
Return type:

int4
Alias:

strpos
Example: 
select locate('high', 'ig', 1);
> 2
strposb(source text, target text)

Returns the binary location of specified substring.

Parameters:
  • source – source string
  • target – target substring
Return type:

int4
Example: 
select strpos('tajo', 'aj');
> 2
substr(source text, start int4, length int4)

Extract substring.

Parameters:
  • source – source string
  • start – start index
  • length – length of substring
Return type:

text
Example: 
select substr('alphabet', 3, 2);
> ph
trim(string text[, characters text])

Removes the characters (a space by default) from the start/end/both ends of the string.

Parameters:
  • string – input string
  • characters – characters which will be removed
Return type:

text
Example: 
select trim('xTajoxx', 'x');
> Tajo
trim([leading | trailing | both] [characters text] FROM string text)

Removes the characters (a space by default) from the start/end/both ends of the string.

Parameters:
  • string – input string
  • characters – characters which will be removed
Return type:

text
Example: 
select trim(both 'x' from 'xTajoxx');
> Tajo
btrim(string text[, characters text])

Removes the characters (a space by default) from the both ends of the string.

Parameters:
  • string – input string
  • characters – characters which will be removed
Return type:

text
Alias:

trim
Example: 
select btrim('xTajoxx', 'x');
> Tajo
ltrim(string text[, characters text])

Removes the characters (a space by default) from the start ends of the string.

Parameters:
  • string – input string
  • characters – characters which will be removed
Return type:

text
Example: 
select ltrim('xxTajo', 'x');
> Tajo
rtrim(string text[, characters text])

Removes the characters (a space by default) from the end ends of the string.

Parameters:
  • string – input string
  • characters – characters which will be removed
Return type:

text
Example: 
select rtrim('Tajoxx', 'x');
> Tajo
split_part(string text, delimiter text, field int)

Splits a string on delimiter and return the given field (counting from one).

Parameters:
  • string – input string
  • delimiter – delimiter
  • field – index to field
Return type:

text
Example: 
select split_part('ab_bc_cd','_',2);
> bc
regexp_replace(string text, pattern text, replacement text)

Replaces substrings matched to a given regular expression pattern.

Parameters:
  • string – input string
  • pattern – pattern
  • replacement – string substituted for the matching substring
Return type:

text
Example: 
select regexp_replace('abcdef', '(ˆab|ef$)', '–');
> –cd–
upper(string text)

Makes an input text to be upper case.

Parameters:string – input string
Return type:text
Example: 
select upper('tajo');
> TAJO
lower(string text)

Makes an input text to be lower case.

Parameters:string – input string
Return type:text
Example: 
select lower('TAJO');
> tajo
lpad(source text, number int4, pad text)

Fill up the string to length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right).

Parameters:
  • source – source string
  • number – padding length
  • pad – padding string
Return type:

text
Example: 
select lpad('hi', 5, 'xy');
> xyxhi
rpad(source text, number int4, pad text)

Fill up the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated.

Parameters:
  • source – source string
  • number – padding length
  • pad – padding string
Return type:

text
Example: 
select rpad('hi', 5, 'xy');
> hixyx
quote_ident(string text)

Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled.

Parameters:string – input string
Return type:text
Example: 
select quote_ident('Foo bar');
> "Foo bar"
repeat(string text, number int4)

Repeat string the specified number of times.

Parameters:
  • string – input string
  • number – repetition number
Return type:

text
Example: 
select repeat('Pg', 4);
> PgPgPgPg
reverse(string text)

Reverse string.

Parameters:string – input string
Return type:text
Example: 
select reverse('TAJO');
> OJAT