General Usage Text Expressions

Commonly Used Text Expressions

Common Text Expressions

Analyze ExpressionReturn TypeDescriptionExampleResult
func.concat(string, string)textString concatenationconcat(‘Post’, ‘greSQL’)PostgreSQL
func.bit_length(stri ng)intNumber of bits in stringbit_length(‘jose’)32
func.char_length(str ing) or func.character_length(string)intNumber of characters in stringchar_leng th(‘jose’)4
func.convert(string using conversion_name)textChange encoding using specified conversion name. Conversions can be defined by CREATE CONVERSION. Also there are some pre-defined conversion names. SeeTable 9-7for available conversion names.convert(‘P ostgreSQL’ using iso_8859_1_to_ut f8)PostgreSQ L’ in UTF8 (Unicode, 8-bit) encoding
func.lower(string)textConvert string to lower caselower(‘TOM ‘)tom
func.octet_length(st ring)intNumber of bytes in stringoctet_length(‘jose’ )4
func.overlay(string placing string from int [forint])textReplace substringoverlay(‘Txxxxas’ placing ‘hom’ from 2 for 4)Thomas
func.position(substring in string)intLocation of specified substringposition(‘om’ in ‘Thomas’)3
func.substring(string [from int] [for int])textExtract substringsubstring( ‘Thomas’ from 2 for 3)hom
func.substring(string frompattern)textExtract substring matching POSIX regular expression.substring( ‘Thomas’ from ‘…$’)mas
func.substring(string frompatternforescape)textExtract substring matching SQL regular expression.substring( ‘Thomas’ from ‘%#”o_a#” _’ for ‘#’)oma
func.trim([leading, trailing, both] [characters] from string)textRemove the longest string containing only the characters (a space by default) from the start/end/both ends of the stringtrim(both ‘x’ from ‘xTomxx’)Tom
func.upper(string)textConvert string to uppercaseupper(‘tom ‘)TOM
func.ascii(string)intASCII code of the first byte of the argumentascii(‘x’)120
func.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 stringbtrim(‘xyx trimyyx’, ‘xy’)trim
func.chr(int)textCharacter with the given ASCII codechr(65)A
func.convert(string text, [src_encoding name,]dest_encoding name)textConvert string to dest_encoding . The original encoding is specified by src_encoding. If src_encoding is omitted, database encoding is assumed.convert( ‘text_in_utf8’, ‘UTF8’, ‘LATIN1’)text_in_utf8 represent ed in ISO 8859-1 encoding
func.decode(string text, type text)Decode binary data from string previously encoded with encode. Parameter type is same as in encode.
func.decode(string text, type text)byteaDecode binary data from string previously encoded with encode. Parameter type is same as in encode.
func.initcap(string)textConvert the first letter of each word to uppercase and the rest to lowercase. Words are sequences of alphanumeric characters separated by non-alphanumer ic characters.initcap(‘hi THOMAS’)Hi Thomas
func.length(string)intNumber of characters in stringlength(‘jose’)4
func.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
func.ltrim(string text [, characters text])textRemove the longest string containing only characters from characters (a space by default) from the start of stringltrim(‘zzz ytrim’, ‘xyz’)trim
func.md5(string)textCalculates the MD5 hash of string, returning the result in hexadecimalmd5(‘abc’)900150983 cd24fb0 d6963f7d2 8e17f72
func.quote_literal(s tring)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.quote_lit eral( ‘O’Reilly’ )O’’Reilly ‘
func.regexp_replace( string text, pattern text,replacement text [,flags text])textReplace substring matching POSIX regular expression. SeeSection 9.7for more information on pattern matching.regexp_re place(‘Tho mas’, ‘.[mN]a.’, ‘M’)ThM
func.repeat(string text, number int)textRepeat string the specified number of timesrepeat(‘Pg ‘, 4)PgPgPgPg
func.replace(string text, from text, to text)textReplace all occurrences in string of substring from with substring toreplace( ‘abcdefabc def’, ‘cd’, ‘XX’)abXXefabX Xef
func.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
func.rtrim(string text [, characters text])textRemove the longest string containing only characters from characters (a space by default) from the end of stringrtrim(‘trimxxxx’, ‘x’)trim
func.split_part(stri ng text, delimiter text, field int)textSplit string on delimiter and return the given field (counting from one)split_par t(‘abc:s ub:@de f@ ghi’, ‘:sub:@’ , 2)def
func.strpos(string, substring)intLocation of specified substring (same as position(subst ring in string), but note the reversed argument order)strpos(‘hi gh’, ‘ig’)2
func.substr(string, from [, count])textExtract substring (same as substring(stri ng from from for count))substr(‘al phabet’, 3, 2)ph
func.to_ascii(string text [, encoding text])textConvert string to ASCII from another encoding (only supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings)to_ascii( ‘Karel’)Karel
func.to_hex(number int or bigint)textConvert number to its equivalent hexadecimal representationto_hex(21 47483647)7fffffff
func.translate(string text, from text, to text)textAny character in string that matches a character in the from set is replaced by the corresponding character in the to settranslate( ‘12345’, ‘14’, ‘ax’)a23x5
Last modified April 06, 2022 at 10:08 AM EST: Initial Documentation Commit (371ae8f)