postgresql字符串函数全集

1. trim

删除字符串头部/两端/尾部的指定字符

trim([leading | trailing | both] [characters] from string)

示例：去掉字段首尾多余空格

update dim_product_mapping set pmd_program_name = trim(leading ' ' from pmd_program_name);
update dim_product_mapping set pmd_program_name = trim(trailing ' ' from pmd_program_name);

update dim_product_mapping set pmd_project_name = trim(leading ' ' from pmd_project_name);
update dim_product_mapping set pmd_project_name = trim(trailing ' ' from pmd_project_name);

update dim_product_mapping set npi_product_name = trim(leading ' ' from npi_product_name);
update dim_product_mapping set npi_product_name = trim(trailing ' ' from npi_product_name);

2. split_part

按指定字符，截取字符串中的某部分

SELECT SPLIT_PART('123','.',1)
//
SELECT SPLIT_PART('123.234.345.456','.',2)
//
SELECT SPLIT_PART('123.234.345.456','.',3)
//
SELECT SPLIT_PART('123.234.345.456','.',4)

实操：
实现 Wn 变成 W(n-2)，如图

update dim_pss_calendar set fiscal_week=concat('W',split_part(fiscal_week,'W',2)::int2-2)  where CHAR_LENGTH(fiscal_week)<=3
update dim_pss_calendar set fiscal_week=concat('W',split_part(split_part(fiscal_week,'W',2),'-',1)::int2-2,'-',split_part(split_part(fiscal_week,'W',2),'-',2))  where CHAR_LENGTH(fiscal_week)>3

3. substring

3.1 按下标截取字符串

select substring('FY2021' FROM 3 for 2)

3.2 截取字母部分和数字部分

with my_data(sdk) as (
values
    ('JavaScript123'),
    ('JavaScript12'),
    ('Android3'),
    ('Android32'),
    ('Swift45'),
    ('Swift100')
)
select  sdk
from my_data 
order by substring(sdk from '[^\d/]*') , substring(sdk from '\d.*')::int 

3.3 字符串正则表达式

符号	含义	实例	实例结果
.	一个点代表一个字符	substring(‘Thomas’ from ‘…$’)	mas
%	代表一个或多个字符
_	代表一个字符
^	代表字符前	substring(‘Thomas’ from ‘^…’)	Thom
$	代表字符后	substring(‘Thomas’ from ‘…$’)	mas
#	分割字符，可以在#"前中后限定选择你最想要的字符	substring(‘Thomas’ from ‘%#“o_a#”_’ for ‘#’ )	oma

参考：
https://blog.csdn.net/mochou111/article/details/100892037

4 contain

postgre没有contain，但是可以采用下面两种等效方法

select * from dim_pss_calendar where week like 'W%' limit 1 ;
select  * from dim_pss_calendar where position('W' in week)>0  limit 1 ;

5 case when

now() between xx and yy

SELECT 
npi_product_name,ok2pilot,sle
,case 
when now()<ok2pilot then 'Pre-Pilot' 
when now() BETWEEN ok2pilot and sle then 'Post-Pilot' 
end status   
FROM datahub_project_schedule 
WHERE product_status='256a0b4c9ee04590ad86ff5ccda9310a'

6. replace

替换源字符串1中指定字符串2为指定字符串3；

SELECT product,max(replace(replace(replace(pss_version,'_',''),'POR',''),'CWV','')::bigint) as version FROM v_business_case_quarterly GROUP BY product 

7. coalesce

取第一个不为空的值，不一定要字符串

select COALESCE(null,null); //报错
select COALESCE(null,null,now(),''); //结果会得到当前的时间
select COALESCE(null,null,'',now()); //结果会得到''

8. length

获取字符串长度

select * from bnbv  where length(design_type)>10;

9.查询指定的字段

意思是从代码中传入查询的字段到sql中，需要去掉字符串外层的双引号。
如下：

order by 
${@org.apache.commons.lang.StringEscapeUtils@escapeSql(param.sortName)}  
${@org.apache.commons.lang.StringEscapeUtils@escapeSql(param.sortBy)}