PostgreSQL 常用字符操作函数

官方文档:https://www.postgresql.org/docs/9.1/functions-string.html

Function Example Result
string || string ‘Post’ || ‘greSQL’ PostgreSQL
string || non-string or non-string || string ‘Value: ‘ || 42 Value: 42
bit_length(string) bit_length(‘jose’) 32
char_length(string) or character_length(string) char_length(‘jose’) 4
lower(string) lower(‘TOM’) tom
octet_length(string) octet_length(‘jose’) 4
overlay(string placing string from int [for int]) overlay(‘Txxxxas’ placing ‘hom’ from 2 for 4) Thomas
position(substring in string) position(‘om’ in ‘Thomas’) 3
substring(string [from int] [for int]) substring(‘Thomas’ from 2 for 3) hom
substring(string from pattern) substring(‘Thomas’ from ‘…$’) mas
substring(string from pattern for escape) substring(‘Thomas’ from ‘%#”o_a#”_’ for ‘#’) oma
trim([leading | trailing | both] [characters] from string) trim(both ‘x’ from ‘xTomxx’) Tom
upper(string) upper(‘tom’) TOM
ascii(string) ascii(‘x’) 120
btrim(string text [, characters text]) btrim(‘xyxtrimyyx’, ‘xy’) trim
chr(int) chr(65) A
concat(str “any” [, str “any” [, …] ]) concat(‘abcde’, 2, NULL, 22) abcde222
concat_ws(sep text, str “any” [, str “any” [, …] ]) concat_ws(‘,’, ‘abcde’, 2, NULL, 22) abcde,2,22
convert(string bytea, src_encoding name, dest_encoding name) convert(‘text_in_utf8’, ‘UTF8’, ‘LATIN1’) text_in_utf8 represented in Latin-1 encoding (ISO 8859-1)
convert_from(string bytea, src_encoding name) convert_from(‘text_in_utf8’, ‘UTF8’) text_in_utf8 represented in the current database encoding
convert_to(string text, dest_encoding name) convert_to(‘some text’, ‘UTF8’) some text represented in the UTF8 encoding
decode(string text, format text) decode(‘MTIzAAE=’, ‘base64’) \x3132330001
encode(data bytea, format text) encode(E’123\000\001’, ‘base64’) MTIzAAE=
format(formatstr text [, str “any” [, …] ]) format(‘Hello %s, %1$s’, ‘World’) Hello World, World
initcap(string) initcap(‘hi THOMAS’) Hi Thomas
left(str text, n int) left(‘abcde’, 2) ab
length(string) length(‘jose’) 4
length(string bytea, encoding name) length(‘jose’, ‘UTF8’) 4
lpad(string text, length int [, fill text]) lpad(‘hi’, 5, ‘xy’) xyxhi
ltrim(string text [, characters text]) ltrim(‘zzzytrim’, ‘xyz’) trim
md5(string) md5(‘abc’) 900150983cd24fb0 d6963f7d28e17f72
pg_client_encoding() pg_client_encoding() SQL_ASCII
quote_ident(string text) quote_ident(‘Foo bar’) “Foo bar”
quote_literal(string text) quote_literal(E’O\’Reilly’) ‘O’’Reilly’
quote_literal(value anyelement) quote_literal(42.5) ‘42.5’
quote_nullable(string text) quote_nullable(NULL) NULL
quote_nullable(value anyelement) quote_nullable(42.5) ‘42.5’
regexp_matches(string text, pattern text [, flags text]) regexp_matches(‘foobarbequebaz’, ‘(bar)(beque)’) {bar,beque}
regexp_replace(string text, pattern text, replacement text [, flags text]) regexp_replace(‘Thomas’, ‘.[mN]a.’, ‘M’) ThM
regexp_split_to_array(string text, pattern text [, flags text]) regexp_split_to_array(‘hello world’, E’\s+’) {hello,world}
regexp_split_to_table(string text, pattern text [, flags text]) regexp_split_to_table(‘hello world’, E’\s+’) hello world (2 rows)
repeat(string text, number int) repeat(‘Pg’, 4) PgPgPgPg
replace(string text, from text, to text) replace(‘abcdefabcdef’, ‘cd’, ‘XX’) abXXefabXXef
reverse(str) reverse(‘abcde’) edcba
right(str text, n int) right(‘abcde’, 2) de
rpad(string text, length int [, fill text]) rpad(‘hi’, 5, ‘xy’) hixyx
rtrim(string text [, characters text]) rtrim(‘trimxxxx’, ‘x’) trim
split_part(string text, delimiter text, field int) split_part(‘abc~@~def~@~ghi’, ‘~@~’, 2) def
strpos(string, substring) strpos(‘high’, ‘ig’) 2
substr(string, from [, count]) substr(‘alphabet’, 3, 2) ph
to_ascii(string text [, encoding text]) to_ascii(‘Karel’) Karel
to_hex(number int or bigint) to_hex(2147483647) 7fffffff
translate(string text, from text, to text) translate(‘12345’, ‘143’, ‘ax’) a2x5