PostgreSql数据库常用字符串处理函数

1 字符串介绍

类型名称	描述
character varying(n) varchar(n)	变长，最大位1GB。存储空间位：4+实际字符串长度。与MySQL中的varchar(n)或text(n)以及Oracle中的varchar2(n)类型类似，但是在MySQL中varchar最长为64KB,oracle varchar2最多为4000字节，而Postgresql中可以达到1GB
character(n) char(n)	定长，不足补空白，最大为1GB。存储空间为4+n
text	变长，无长度限制。与MySQL中的longtext相类似。

2 字符串函数与操作符

函数	返回类型	描述	示例	结果
string || string	text	字符串连接	‘Post’||‘greSQL’	PostgreSQL
bit_length(string)	int	字符串中二进制的个数	bit_length(‘jose’)	32
char_length(string)或character_length(string)	int	字符串中的字符个数	char_length(‘数据库’)	3
convert(string using conversion_name)	text	使用指定的转换名字改变编码。转换可以CREATE COVERSION来定义。当然，系统中也有一些预定义的转换名字。	convert(‘PostgreSQL’,using iso_8859_1_to_utf8)	UTF8编码的’PostgreSQL’
lower(string)	text	把字符串转换为小写	lower(‘TOM’)	tom
octet_length(string)	int	字符串中的字节数	octer_length(‘jose’)	4
overlay(string placing string from int[for int])	text	替换子字符串	overlay(‘Txxxxas’, placing ‘hom’ from 2 for 4)	Thomas
position(substring in string)	int	指定的子字符串的位置	position(‘om’ in ‘Thomas’)	3
substring(string [from int] [for int])	text	抽取子字符串	substring(‘Thomas’ from 2 for 3)	hom
substring(string from pattern)	text	抽取匹配POSIX正则表达式的子字符串	substring(‘Thomas’ from ‘…$’)	mas
substring(string from pattern for escape)	text	抽取匹配SQL正则表达式的子字符串	substring(‘Thomas’ from ‘%#“o_a#”_’ for ‘#’)	oma
trim([leading|trailing|both][characters] from string)	text	从字符串string开头|结尾|两端删除只包含characters中字符（默认是一个空白)的最长的字符串	trim(both ‘x’ from ‘xTomxx’)	Tom
upper(string)	text	把字符串抓换为大写	upper(‘tom’)	TOM

3 其他字符串函数

函数	返回类型	描述	示例	结果
ascii(string)	int	参数第一个字符的ASCII码	ascii(‘a’)	97
btrim(string text [,characters text])	text	从string的开头和结尾删除包含在参数chatacters中的字符，直到遇到一个不是在characters中的字符为止，参数characters的默认值为空格	btrim(‘aaosdbaaa’,'aa)	osdb
chr(int)	text	给出ASCII码的字符	chr(97)	a
convert(string text,[src_encoding name,]dest_encoding name)	text	把原来编码为src_encoding的字符串转换为dest_encoding编码（如果省略了src_encoding,将使用数据库编码）	convert(‘aa’,‘UTF8’,‘GBK’)	\x6161（以GBK编码表示的aa)
decode(string text,type text)	bytea	把原来用encode编码的string中的二进制数据解码。参数类型与encode相同	decode(‘b3NkYmEAAQ==’,‘base64’)	\x6f736462610001
encode(data bytea,type text)	text	把二进制数据编码为只包含ASCII形式的数据	encode(E’osdba\\000\\001’,‘base64’)	b3NkYmEAAQ==
intcap(string)	text	把每个单词的第一个字母转换为大写，其他的仍为小写。单子是一系列字母数字组成的字符串，用非字母数字分割	initcap(‘hi osdba’)	Hi Osdba
length(string)	int	string中字符的数目	length(‘osdba’)	5
lpad(string text,length int,[,fill text])	text	通过填充字符fill(默认为空白)，把string填充为length长度。如果string已经比length长，则将其尾部截断	lpad(‘OK’,5,‘12’)	121OK
ltrim(string)	text	从字符串string的开头删除包含在参数characters中的字符，直到遇到一个不在characters中的字符为止，参数characters的默认值为空格	ltrim(‘213osdba213’,‘123’)	osdba213
md5(string)	text	计算string的md5散列，以十六进制返回结果	md5(‘osdba’)	bc4e68be5b31f23d8d56c7f4c3351fec
pg_client_encoding	name	返回当前客户端的编码名称	pg_client_encoding()	GBK
quote_ident(string)	text	返回适用于SQL语句的标识符形式(使用适当的引号来界定)。只有在必要的时候才会添加引号（字符串中包含非标识符字符或者转换大小写的字符）。嵌入的引号会被恰当地写为双份	quote_ident(‘osdba’)	“osdba”
quote_literal(string)	text	返回适用于SQL语句中当作文本使用的形式。嵌入的引号和反斜杠被恰当的写为双份	quote_literal('O\‘Reilly’)	‘O"Relily’
regexp_replace(string text,pattern text,replacement text[,flags text])	text	替换匹配POSIX正则表达式的字符串	regexp_replace(‘os123dba’,‘.[1-9]+’,‘#’)	o#dba
repeat(string text,number int)	text	将string重复number次	repeat(‘osdba’,3)	osdbaosdbaosdba
replace(string text，from text,to text)	text	把字符串string中出现的所有子字符串from替换为子字符串to	replace(‘123osdba45’osdba78,‘osdba’,’-')	123-45-78
rpad(string text,length int [,fill text])	text	通过填充字符fill(默认为空白)，把string填充为length长度。如果string已经比length长，则将其尾部截断	rpad(‘os’,6,'123)	os1231
rtrim(string text[,characters text])	text	从字符串string的结尾删除包含在参数characters中的字符，直到遇到一个不包含在characters中的字符为止，参数characters的默认值为空格	rtrim(‘trimxxxx’,‘x’)	trim
split_part(string text,delimiter text,field int)	text	根据delimiter分隔string返回生成的第fieldde 子字符串(1为基)	split_part(‘123#456#789’,‘#’,2)	456
strpos(string,substring)	int	指定子字符串的位置。和position(substring in string)一样，不过参数顺序相反	strpos(‘osdba’,‘db’)	3
substr(string,from [,count])	text	抽取字符串。和substring(string from from for count)一样	substr(‘osdba’,2,2)	sd
to_ascii(string text [,encoding text])	text	把string从其他编码抓换为ASCII(仅支持LATIN1、LATIN2、LATIN9、WIN11250编码)	to_ascii(‘Osdba’)	Osdba
to_hex(number int 或bigint)	text	把number转换成十六进制表现形式	to_hex(‘2147836647’)	7fffffff
translate(string text,from text ,to text)	text	把string中包含所有的匹配from的字符转换为对应的在to中的字符	translate(‘12345’,‘14’,‘db’)	d23b5