PostgreSQL常用字符串函数与示例说明

coalesce

coalesce主要用来处理空，它返回第1个不为空的值，可以接受整型或者字符串，但是不能混有。

select coalesce('a','0') as r1,coalesce(1,2) as r2,coalesce(null,2,1) as r3,coalesce(null,NULL,'first') as r4;

字符串位置(position strpos)

返回子字符串在字符串中的位置,有点像Java中的index

select position('456' in '123456789') as r1,strpos('123456789', '678') as r2;

字符串长度与大小写转换

select length('hello world') as len, upper('Hello World') as up, lower('Hello World') as lo;

去掉空格(trim ltrim rtrim)

select trim(' Hello World ') as t,
rtrim(' Hello World ') as r,
ltrim(' Hello World ') as l;

字符串连接(concat)

select concat('Hello', ' ', 'World');
select concat(ip, ' ', port) as id from user;

PostgreSQL从9.1开始提供了||操作符，可以用来代替concat函数。

select ip || ' ' || port as id from user;

字符串替换

简单替换(replace)

-- 第1个参数是源字符串，第2个参数是需要的替换的old，第3个参数是替换之后的new
select replace('啊哈, 娘子', '娘子', '相公') as r;

替换指定位置长度(overlay)

overlay(string placing substring from start [for length]): 用另一个字符串替换字符串的一部分。

-- 结果：123中文6干嘛orld
-- 将第7位开始的2位替换为了干嘛，其他不变
select overlay('123中文67world' placing '干嘛' from 7);

-- 结果:123中文6干嘛rld
-- 将第7位开始的3位替换为了干嘛，其他不变
select overlay('123中文67world' placing '干嘛' from 7 for 3);

正则替换(regexp_replace)

regexp_replace(string, pattern, replacement [, flags ]): 使用正则表达式替换字符串中的子字符串。

select regexp_replace('Hello123 World456', '\d+', '替换之后的内容', 'g') as r;

字符串匹配

regexp_matches(string, pattern [, flags ]): 使用正则表达式匹配字符串中的子字符串。

-- \b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b

-- 返回第1个匹配数组
select regexp_matches('123c929 33哈哈123 33hh123','(\d+)[a-z]+(\d+)') as r;

-- 返回全部匹配数组
select regexp_matches('123c929 33哈哈123 33hh123','(\d+)[a-z]+(\d+)','g') as r;

字符串拆分

split_part(拆分数组取指定位置的值)

select split_part('1-2-3', '-', 2) as r;

string_to_array(拆分为数组)

select 
string_to_array('1,2,3', ',') as r1,
string_to_array('1,2,3', ',', '2') as r2;

regexp_split_to_array(拆分为数据，使用正则表达式)

除了使用固定字符拆分，还可以使用正则表达式拆分：

select regexp_split_to_array('1ab22cd3ef4', '[a-z]+') as result;

regexp_split_to_table(拆分为表，多行)

除了拆分为数组，还可以拆分为table

SELECT
regexp_split_to_table('1,2,3,4,5,6', ',' ) as r1,
regexp_split_to_table('1。2。3。4。5。6', '。' ) as r2,
regexp_split_to_table('1|2|3|4|5|6', E'\\|') as r3,
regexp_split_to_table('1 2 3', ' ') as r4;

字符串取子串

1. substring(content,start,length):第1个参数是要截取的子串，第2个参数是开始位置，从1开始，第3个参数是截取长度(可选,默认取到最后1个)
2. substring(content from start for length):上1个的单参数模式
3. substring(content,pattern):可以使用正则表达式

基本用法

start从1开始，如果小于1，自动修正为1，length如果大于最大长度，自动修正为最大长度。

select substring('Hello World',1,6) as S1L6,
substring('Hello World',1) as S1,
substring('Hello World',0) as S0,
substring('Hello World',0,20) as S0L20;

substring还有一个等价的函数substr：

select substr('Hello World',1,6) as S1L6,
substr('Hello World',1) as S1,
substr('Hello World',0) as S0,
substr('Hello World',0,20) as S0L20;

单参数

select substring('Hello World' from 1 for 6) as S1L6,
substring('Hello World' from 1) as S1,
substring('Hello World' from 0) as S0,
substring('Hello World' from 0 for 20) as S0L20;

-- 截取时间就非常方便了
select substring('2050-01-01 12:00:00' from 1 for 10);

正则截取

还可以使用正则截取：

-- 截取第1个匹配
select substring('hello world c909 what the world c919 hi c929','([0-9]{1,4})') as r;

下面这个匹配哪一个？

select substring('what theA1234567890the ok hahahaA987654321','.*(A\d{5}).*') AS r;

答案是：A98765,因为第1个.*是贪婪模式。

截取特定子串：

-- 截取从are开始的串
select substring('what are world',position('are' in 'what are world')) as r;

left与right(左右截取)

-- 按字符长度，不是字节
select left('你好，hi,in the world',5) as r1,
right('你好，hi,in the world',5);

正则

元字符

1. |：表示选择两个候选项之一
2. *：表示重复前面的项0次或更多次
3. +：表示重复前面的项1次或更多次
4. ?：表示重复前面的项0次或1次
5. {m}：表示重复前面的项m次
6. {m，}：表示重复前面的项m次或更多次
7. {m，n}：表示重复前面的项至少m次，不超过n次
8. ()：匹配分组
9. []：可选组

like与等价符号

1. %代表0个或任意个字符
2. _代表任意1个字符
3. 如果想匹配%、_自身，可以使用反斜杠\转义
4. 可以使用escape指定转义字符

CREATE TABLE public."user" (
	id serial4 NOT NULL,
	"name" varchar NULL,
	CONSTRAINT newtable_pk PRIMARY KEY (id)
);

INSERT INTO public."user" ("name") VALUES
	 ('bob'),
	 ('boob'),
	 ('bo%b'),
	 ('Boob'),
	 ('Bo%b'),
	 ('BoB'),
	 ('B_b'),
	 ('BooB'),
	 ('b_b');

PostgreSQL的like比较灵活，可以有not like取反，也有ilike不区分大小写

select * from public.user where name like 'al%';
select * from public.user where name like 'al_';

select * from public.user where name like 'bo%b';
select * from public.user where name like 'bo_b';
select * from public.user where name ilike 'bo_b';
select * from public.user where name like 'bo\%b';
select * from public.user where name like 'bo#%b' escape '#';
select * from public.user where name not like 'bo#%b' escape '#';

PostgreSQL还提供了如下与like等价的操作符：

1. ~~：等价于like
2. ~~*：like不区分大小写
3. !~~：等价于not like
4. !~~*：not like不区分大小写

select * from public.user where name ~~ 'bo_b';
select * from public.user where name ~~* 'bo_b';
select * from public.user where name !~~ 'bo\%b';
select * from public.user where name !~~* 'bo\%b';

正则匹配

PostgreSQL除了like，还支持正则匹配，这个就慎用了，可以作为的附加条件，而不要作为过滤的主要条件，特别是大表。

1. ~：匹配正则表达式，区分大小写
2. ~*：匹配正则表达式，不区分大小写
3. !~：不匹配正则表达式，区分大小写
4. !~*：不匹配正则表达式，不区分大小写

select * from public.user where name ~ '(B|b)oob';
select * from public.user where name ~* 'boob';
select * from public.user where name !~ 'boob';
select * from public.user where name !~* 'boob';