前言
本文基于PostgreSQL 12.6版本，不同版本的函数可能存在差异。
拼接字符串
||是字符串连接操作符，在拼接字符串时要求前两个操作数至少有一个是字符串类型，不然会报错。如下：
1
2
3
| select 'a' || 1;
select 2 || 'a' || 1;
select 2 || 44 || 'a' || 1; --Error
|
填充字符串
lpad(string text, length int [, fill text])是在字符串左边填充字符，如果不指定要填充的字符，则默认填充空格，如下：
1
2
3
| select LPAD((99 - 1)::text, 6); -- 98
select LPAD((99 - 1)::text, 6, '0'); --000098
select LPAD((99 + 1)::text, 6, 'ab'); --aba100
|
rpad函数与lpad函数相反，是在字符串右边填充字符。
大小写转换
upper和lower函数，如下：
1
2
| select upper('test'); --TEST
select lower('TEST'); --test
|
获取字符串长度
length、char_length和character_length函数，如下：
1
2
3
| select length('test'); --4
select char_length('test'); --4
select character_length('test'); --4
|
截取字符串
substring函数，支持下标范围截取或者正则表达式截取，如下：
1
2
| select substring('PostgreSQL' from 2 for 4); --ostg
select substring('PostgreSQL' from '[a-z]+'); --ostgre
|
也可以用substr函数，如下：
1
2
3
4
| select substr('PostgreSQL', 2, 0); --空字符串
select substr('PostgreSQL', 2, 1); --o
select substr('PostgreSQL', 2, 4); --ostg
select substr('PostgreSQL', 2); --ostgreSQL
|
裁剪字符串
trim函数，从字符串的开头/结尾/两边（leading/trailing/both）尽可能多地裁剪指定的字符，不指定则裁剪空白符，如下：
1
2
3
4
5
6
| select trim(leading 'x' from 'xTestxx'); --Testxx
select trim(trailing 'x' from 'xTestxx'); --xTest
select trim(both 'x' from 'xTestxx'); --Test
select trim(both from ' Test '); --Test
select trim(' Test '); --Test
|
也可以用ltrim，rtrim或者btrim函数，效果同上：
1
2
3
| select ltrim('xTestxxy', 'xy'); --Testxxy
select rtrim('xTestxxy', 'xy'); --xTest
select btrim('xTestxxy', 'xy'); --Test
|
获取第一个字符的ASCII码
ascii函数，如下：
1
2
| select ascii('test'); --116
select ascii('t'); --116
|
如果想从ASCII码转成字符，则使用chr函数，参数是int，如下：
计算string的MD5散列
md5函数，以十六进制返回结果，如下：
1
| select md5('abc'); --900150983cd24fb0d6963f7d28e17f72
|
null和'‘的区别与判断以及COALESCE函数
null是一种类型，''是空字符串，打个比方，''是你参加了考试且得了零分，而null则是你压根就没有参加考试。
如果要在sql中对两者进行判断，是有区别的：
1
2
3
4
5
6
7
8
9
10
11
| --null只能和is或is not搭配，不能使用=、!=或者<>
select * from student where name is null;
select * from student where name is not null;
--''的判断可以使用=、!=或者<>
select * from student where name = '';
select * from student where name != '';
select * from student where name <> '';
--任何与null的运算比较，结果都是null
select 1 > null; --null
|
COALESCE函数是返回参数中的第一个非null的值，在PostgreSQL 10里，它要求参数中至少有一个是非null的，如果参数都是null会报错。
不过在PostgreSQL 12.6版本COALESCE函数允许参数里只有null，此时返回值是null。
1
2
3
4
5
6
| select COALESCE(null,null); //报错
select COALESCE(null,null,now()::varchar,''); //结果会得到当前的时间
select COALESCE(null,null,'',now()::varchar); //结果会得到''
//可以和其他函数配合来实现一些复杂点的功能：查询学生姓名，如果学生名字为null或''则显示“姓名为空”
select case when coalesce(name,'') = '' then '姓名为空' else name end from student;
|
nullif函数
nullif(a, b)用来检测a参数是否与b参数相等，这里的a、b参数必须是同一种数据类型，否则会报错。当a参数与b参数相等时会返回null，否则返回a参数。
可以用这个函数来检测期望以外的值，一般用于检测字符串比较多。如下：
1
2
3
4
| select nullif('test', 'unexpected'); --test
select nullif('unexpected', 'unexpected'); --null
select nullif(233, 111); --233
|
参考链接
