Skip to main content

String Functions and Operators

This section describes functions and operators for examining and manipulating string values. Unless otherwise noted, all of the functions listed below work on all string types, but be wary of potential effects of automatic space-padding when using the whitespace paddedcharacter type.

SQL defines some string functions that use key words, rather than commas, to separate arguments. The strings defined in the SQL standard are shown in the table below:

FunctionReturn TypeDescriptionExample
string || stringtextString concatenation. NULL arguments are not ignored, in contrast to concat.'Hy'||'per''Hyper' 'Hy'||NULLNULL
string || non-string
or
non-string || string
textString concatenation with one non-string input'Value: '||42'Value: 42'
bit_length(string)intNumber of bits in stringbit_length('jose')32
char_length(string)
or
character_length(string)
intNumber of characters in stringchar_length('jose')4
lower(string)textConvert string to lower caselower('TOM')'tom'
octet_length(string)intNumber of bytes in stringoctet_length('jose')4
overlay(string placing string from int for int)textReplace substringoverlay('Txxxxas' placing 'hom' from 2 for 4)'Thomas'
position(substring in string)intLocation of specified substringposition('om' in 'Thomas')3
substring(string from int for int)textExtract substringsubstring('Thomas' from 2 for 3)'hom'
trim(leading | trailing | both characters from string)textRemove the longest string containing only characters from characters (a space by default) from the start, end, or both ends (both is the default) of stringtrim(both 'xyz' from 'yxTomxx')'Tom'
upper(string)textConvert string to upper caseupper('tom')'TOM'

Hyper also provides versions of these functions that use the regular function invocation syntax, together with additional, non-standard string manipulation functions. Those functions are:

FunctionReturn TypeDescriptionExample
ascii(string)intASCII code of the first character of the argument. For UTF8 returns the Unicode code point of the character.ascii('x')120
btrim(string text , characters text)textRemove the longest string consisting only of characters in characters (a space by default) from the start and end of string.btrim('xyxtrimyyx', 'xyz')'trim'
chr(int)textCharacter with the given code. For UTF8 the argument is treated as a Unicode code point. The NULL (0) character is not allowed because text data types cannot store such bytes.chr(65)'A'
concat(str "any" [, str "any" [, ...] ])textConcatenate the text representations of all the arguments. NULL arguments are ignored, in contrast to ||.concat('abcde', 2, NULL, 22)abcde222
decode(string text, format text)byteaDecode binary data from textual representation in string. Options for format are same as in encode.decode('MTIzAAE=', 'base64')\x3132330001
encode(data bytea, format text)textEncode binary data into a textual representation. Supported formats are: base64, hex, escape. escape converts zero bytes and high-bit-set bytes to octal sequences (\``<nnn>) and doubles backslashes.encode('123\000\001', 'base64')MTIzAAE=
initcap(string)textConvert the first letter of each word to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.initcap('hi THOMAS')'Hi Thomas'
left(str text, n int)textReturn first <n> characters in the string. When <n> is negative, return all but last |<n>| characters.left('abcde', 2)'ab'
length(string)intNumber of characters in stringlength('jose')4
lpad(string text, length int , fill text)textFill up the string to length 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).lpad('hi', 5, 'xy')'xyxhi'
ltrim(string text , characters text)textRemove the longest string containing only characters from characters (a space by default) from the start of string.ltrim('zzzytest', 'xyz')'test'
md5(string)textCalculates the MD5 hash of string, returning the result in hexadecimal.md5('abc')900150983cd24fb0 d6963f7d28e17f72
quote_ident(string text)textReturn 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.quote_ident('Foo bar')'"Foo bar"'
quote_literal(string text)textReturn the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable.quote_literal(E'O\'Reilly')'O''Reilly'
repeat(string text, number int)textRepeat string the specified number of timesrepeat('Hyper!', 2)'Hyper!Hyper!'
replace(string text, from text, to text)textReplace all occurrences in string of substring from with substring to.replace('abcdefabcdef', 'cd', 'XX')'abXXefabXXef'
reverse(str)textReturn reversed string.reverse('abcde')'edcba'
right(str text, n int)textReturn last <n> characters in the string. When <n> is negative, return all but first |<n>| characters.right('abcde', 2)'de'
rpad(string text, length int , fill text)textFill 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.rpad('hi', 5, 'xy')'hixyx'
rtrim(string text , characters text)textRemove the longest string containing only characters from characters (a space by default) from the end of string.rtrim('testxxzx', 'xyz')'test'
split_part(string text, delimiter text, field int)textSplit string on delimiter and return the given field (counting from one).split_part('abc~@~def~@~ghi', '~@~', 2)'def'
strpos(string, substring)intLocation of specified substring (same as position(substring in string), but note the reversed argument order).strpos('high', 'ig')2
substr(string, from , count)textExtract substring (same as substring(string from from for count)).substr('alphabet', 3, 2)'ph'
to_hex(number int or bigint)textConvert number to its equivalent hexadecimal representation.to_hex(2147483647)'7fffffff'