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

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;

postgresql coalesce函数

字符串位置(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;

postgresql去掉空格

字符串连接(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;

postgresql字符串正则替换

字符串匹配

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;

字符串取子串

substring(content,start,length):第1个参数是要截取的子串，第2个参数是开始位置，从1开始，第3个参数是截取长度(可选,默认取到最后1个)
substring(content from start for length):上1个的单参数模式
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);

正则

元字符

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

like与等价符号

%代表0个或任意个字符
_代表任意1个字符
如果想匹配%、_自身，可以使用反斜杠\转义
可以使用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

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

~~：等价于like
~~*：like不区分大小写
!~~：等价于not like
!~~*：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，还支持正则匹配，这个就慎用了，可以作为的附加条件，而不要作为过滤的主要条件，特别是大表。

~：匹配正则表达式，区分大小写
~*：匹配正则表达式，不区分大小写
!~：不匹配正则表达式，区分大小写
!~*：不匹配正则表达式，不区分大小写

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';