网站设计风格及色彩搭配技巧 -,北京 网站建设咨询顾问公司,上海公司注册一站式企业服务,海口市住房和城乡建设局 网站文章目录 coalesce字符串位置(position strpos)字符串长度与大小写转换去掉空格(trim ltrim rtrim)字符串连接(concat)字符串替换简单替换(replace)替换指定位置长度(overlay)正则替换(regexp_replace) 字符串匹配字符串拆分split_part(拆分数组取指定位置的值)string_to_array… 文章目录 coalesce字符串位置(position strpos)字符串长度与大小写转换去掉空格(trim ltrim rtrim)字符串连接(concat)字符串替换简单替换(replace)替换指定位置长度(overlay)正则替换(regexp_replace) 字符串匹配字符串拆分split_part(拆分数组取指定位置的值)string_to_array(拆分为数组) regexp_split_to_array(拆分为数据使用正则表达式)regexp_split_to_table(拆分为表多行)字符串取子串基本用法单参数正则截取left与right(左右截取) 正则元字符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 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;字符串取子串
substring(content,start,length):第1个参数是要截取的子串第2个参数是开始位置从1开始第3个参数是截取长度(可选,默认取到最后1个)substring(content from start for length):上1个的单参数模式substring(content,pattern):可以使用正则表达式
基本用法
start从1开始如果小于1自动修正为1length如果大于最大长度自动修正为最大长度。
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次或更多次{mn}表示重复前面的项至少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等价的操作符
~~等价于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;
