postgresql字符串函数全集

ゞ浴缸里的玫瑰 2022-10-30 15:22 296阅读 0赞

### 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)，如图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaW5taW5uYQ_size_16_color_FFFFFF_t_70]

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

按下标截取字符串

select substring('FY2021' FROM 3 for 2)

参考：  
[https://blog.csdn.net/mochou111/article/details/100892037][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()); //结果会得到''

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaW5taW5uYQ_size_16_color_FFFFFF_t_70]: /images/20221024/a0725da7f0be4ee485eee2e7d28aee4a.png
[https_blog.csdn.net_mochou111_article_details_100892037]: https://blog.csdn.net/mochou111/article/details/100892037