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

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;

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

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

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

-- 第1個(gè)參數(shù)是源字符串，第2個(gè)參數(shù)是需要的替換的old，第3個(gè)參數(shù)是替換之后的new
select replace('啊哈, 娘子', '娘子', '相公') as r;

-- 結(jié)果：123中文6干嘛orld
-- 將第7位開(kāi)始的2位替換為了干嘛，其他不變
select overlay('123中文67world' placing '干嘛' from 7);
-- 結(jié)果:123中文6干嘛rld
-- 將第7位開(kāi)始的3位替換為了干嘛，其他不變
select overlay('123中文67world' placing '干嘛' from 7 for 3);

select regexp_replace('Hello123 World456', '\d+', '替換之后的內(nèi)容', 'g') as r;

-- \b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b
-- 返回第1個(gè)匹配數(shù)組
select regexp_matches('123c929 33哈哈123 33hh123','(\d+)[a-z]+(\d+)') as r;

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

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

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

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

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;

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;

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;
-- 截取時(shí)間就非常方便了
select substring('2050-01-01 12:00:00' from 1 for 10);

-- 截取第1個(gè)匹配
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;

-- 截取從are開(kāi)始的串
select substring('what are world',position('are' in 'what are world')) as r;

-- 按字符長(zhǎng)度，不是字節(jié)
select left('你好，hi,in the world',5) as r1,
right('你好，hi,in the world',5);

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

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

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

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