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

目录

• coale编程客栈sce
• 字符串位置(position strpos)
• 字符串长度与大小写转换
• 去掉空格(trim ltrim rtrim)
• 字符串连接(concat)
• 字符串替换
• 简单替换(replace)
• 替换指定位置长度(overlay)
• 正则替换(regexp_replace)
• 字符串匹配
• 字符串拆分
• split_part(拆分数组取指定位置的值)
• string_to_array(拆分为数组)
• regexp_split_to_array(拆分为数据，使用正则表达式)
• regexp_split_to_table(拆分为表，多行)
• 字符串取子串
• 基本用法
• 单参数
• 正则截取
• 正则
• 元字符
• like与等价符号
• 正则匹配

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 www.devze.comip || ' ' || 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;

字符串取子串

• 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 NULLwww.devze.com,
	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';
selecjavascriptt * from public.user where name like 'bo#%b' escape '#';
select * from public.user where name not like 'bo#%b' escape '#';

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 njsame !~~* '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';

到此这篇关于PostgreSQL常用字符串函数与示例说明的文章就介绍到这了,更多相关PostgreSQL常用字符串函数内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！