PostgreSQL之SQL函数介绍及实践（二）

RDBMS作为管理数据安全，共享，可靠的软件管理系统，需要按照不同的数据模型组织和存储数据，为了方便用户处理数据，任何数据库为了简化业务开发复杂度，提供了丰富的不同数据类型的处理函数。而PostgreSQL除了支持 SQL 标准所支持的函数外，也可以使用不同的服务器编程接口来实现自定义函数，以针对不同的业务需要进行针对性开发。今天来给大家介绍一下PostgreSQL中的字符串处理函数。

一、字符串处理函数

1.1字符串操作符

PostgreSQL对于字符串的操作符，支持关系型数据库中通用的符号 ||，用来多个字符串之间或者空字符和多个字符串之间的连接。
示例：

hrdb=# -- || 为字符串连接操作符
hrdb=# SELECT ‘Postgre‘ || ‘SQL‘ AS result;
   result   
------------
 PostgreSQL
(1 row)

hrdb=# SELECT ‘PostgreSQL‘ || ‘ ‘ || ‘is most advanced open source ORDBMS !‘ AS result;
                      result                      
--------------------------------------------------
 PostgreSQL is most advanced open source ORDBMS !
(1 row)

1.2字符串函数

字符串位数长度函数
bit_length(string)
返回字符串在数据库中的位数长度
示例：

hrdb=# --字符串位数函数 bit_length(string)
hrdb=# SELECT bit_length(‘PostgreSQL‘) AS result;
 result 
--------
     80
(1 row)

统计字符长度函数
char_length(string) 或 character_length(string)
返回传入字符串的字符个数
示例：

hrdb=# --字符串字符长度函数 char_length() 或 character_length()
hrdb=# SELECT char_length(‘PostgreSQL‘) AS result;
 result 
--------
     10
(1 row)

hrdb=# SELECT character_length(‘PostgreSQL 12‘) AS result;
 result 
--------
     13
(1 row)

温馨提示：
注意，使用该函数统计字符串字符长度，将包含空白字符，如果需要将空白字符排除统计，需要做相应的处理。

大小写转换函数
lower(string) 和upper(string)
返回将传入的字符串转换成大(小)写
示例：

hrdb=# --大小写转换函数 lower()和upper
hrdb=# SELECT lower(‘POSTGRESQL‘) AS result;
   result   
------------
 postgresql
(1 row)

hrdb=# SELECT upper(‘postgresql‘) AS result;
   result   
------------
 POSTGRESQL
(1 row)

字符串首字母转换为大写函数
initcap(string)
返回传入字符串的首字母为大写的字符串
示例：

hrdb=# --字符串首字母大写函数 initcap()
hrdb=# SELECT initcap(‘postgreSQL‘) AS result;
   result   
------------
 Postgresql
(1 row)

替换子串函数
overlay()
返回将起始位置到结束位置使用指定的子串替换
示例：

hrdb=# --将起始位置到结束位置的字符使用指定的字符替换
hrdb=# SELECT overlay(‘Postgresql‘ placing ‘SQL‘ from 8 for 10);
  overlay   
------------
 PostgreSQL
(1 row)
hrdb=# --使用该函数如果没有指定结束位置，将根据指定字符串长度
hrdb=# --进行替换
hrdb=# SELECT overlay(‘http://www.google.com‘ placing ‘https‘ from 1);
        overlay        
-----------------------
 https//www.google.com
(1 row)

字符串所占字节统计函数
octet_length()
返回字符串所占字节的长度
示例：

--返回字符串所占字节长度函数 octet_length()
SELECT octet_length(‘PostgreSQL‘) AS result;
SELECT octet_length(‘开源数据库‘) AS result;

温馨提示：
对于中文字符，PostgreSQL 默认使用 UTF8 编码，一个中文占用3个字节。

查找指定字符或字符串在字符串中的位置函数
position()
示例：

hrdb=# --查找指定字符或字符串在字符串中的位置函数
hrdb=# SELECT position(‘pos‘ in ‘$PGDATA/postgresql‘) AS result;
 result 
--------
      9
(1 row)

按指定的位置截取指定字符或字符串函数
substring() 或 substr()
示例：

hrdb=# --按指定的位置截取指定字符或字符串函数 substring() 或 substr()
hrdb=# SELECT substring(‘https://www.baidu.com‘,9) AS result;
    result     
---------------
 www.baidu.com
(1 row)

hrdb=# SELECT substr(‘https://www.baidu.com‘,9,21) AS result;
    result     
---------------
 www.baidu.com
(1 row)

温馨提示：

通常，position函数和substring函数会一起集合使用，方便批量处理字符串。

如提取字段 https://www.baidu.com中的www.baidu.com，我们不能一个一个去数它的位置在哪里，如果字符串短，且容易数，就没有必要去使用position函数定位指定字符串的起始位置了，如果字符串长，且不容易数，那么就需要position函数去定位指定字符的起始位置作为子串截取字符串函数的起始位置了。
示例：

hrdb=> SELECT substring(‘https://www.baidu.com‘,position(‘w‘ in ‘https://www.baidu.com‘)) as result;
    result     
---------------
 www.baidu.com

移除字符串两端多余的字符函数
trim([leading | trailing|both] [characters] from string)
其中参数
leading | trailing | both 表示
移除
开头 | 结尾 | 两端 的字符，默认为 both
leading 相当于 ltrim()函数，表示左移除
trailing 相当于 rtrim()函数，表示右移除
both 相当于 btrim()函数，表示两端移除
示例：

hrdb=> --移除字符串两端多余的字符函数 trim()
hrdb=> --其中参数leading | trailing | both 表示
hrdb=> --移除开头 | 结尾 | 两端 的字符串，默认为 both
hrdb=> --leading 相当于 ltrim()函数，表示左移除
hrdb=> --trailing 相当于 rtrim()函数，表示右移除
hrdb=> SELECT trim(‘rdb ms‘ from ‘rdb PostgreSQL ms‘) AS result;
   result   
------------
 PostgreSQL
(1 row)

hrdb=> SELECT trim(leading ‘rdb ms‘ from ‘rdb PostgreSQL ms‘) AS result;
    result     
---------------
 PostgreSQL ms
(1 row)

hrdb=> SELECT trim(trailing ‘rdb ms‘ from ‘rdb PostgreSQL ms‘) AS result;
     result     
----------------
 rdb PostgreSQL
(1 row)

hrdb=> SELECT ltrim(‘rdb PostgreSQL ms‘,‘rdb ms‘) AS result;
    result     
---------------
 PostgreSQL ms
(1 row)

hrdb=> SELECT rtrim(‘rdb PostgreSQL ms‘,‘rdb ms‘) AS result;
     result     
----------------
 rdb PostgreSQL
(1 row)

字符转换为ASCII码函数
ascii()
返回指定字符的 ascii码值
示例：

hrdb=> --返回指定字符的ASCII码值函数 ascii()
hrdb=> SELECT ascii(‘S‘) AS result;
 result 
--------
     83
(1 row)

hrdb=> SELECT ascii(‘a‘) AS result;
 result 
--------
     97
(1 row)

替换字符串函数
replace()
返回将字符串中出现的字符使用指定的子串替换
示例：

hrdb=> --返回将字符串中出现的字符使用指定的子串替换函数 replace()
hrdb=> SELECT replace(‘http://www.baidu.com‘,‘http‘,‘https‘) AS result;
        result         
-----------------------
 https://www.baidu.com
(1 row)

hrdb=> SELECT replace(‘南京的风向吹向南面‘,‘南‘,‘北‘) AS result;
       result       
--------------------
 北京的风向吹向北面
(1 row)

将字符对应的ascii码值转换为字符函数
chr()
返回指定ascii 码对应的字符
示例：

hrdb=> --返回指定ascii 码对应的字符函数 chr()
hrdb=> -- 39 表示一个单引号 
hrdb=> SELECT chr(39) || first_name || chr(39) AS first_name
hrdb-> FROM employees limit 1;
 first_name 
------------
 ‘Steven‘
(1 row)

hrdb=> -- 10 表示一个换行符号
hrdb=> SELECT replace(t.txt,chr(10),‘ ‘) as result 
hrdb-> FROM 
hrdb-> (SELECT ‘PostgreSQL
hrdb‘> is most
hrdb‘> advanced open source rdbms
hrdb‘> ‘ as txt) as t;
                     result                     
------------------------------------------------
 PostgreSQL is most advanced open source rdbms 
(1 row)

hrdb=> -- 32 表示一个空格符号
hrdb=> SELECT replace(t.txt,chr(32),‘|‘) 
hrdb-> FROM 
hrdb-> (SELECT ‘a        b‘ AS txt) t;
  replace   
------------
 a||||||||b
(1 row)

温馨提示：
chr(9) 表示一个水平制表符，chr(11)表示一个垂直制表符，chr(13)表示一个回车符号。通常chr() 函数结合replace()函数一起使用，示例如上。

字符串拼接函数
concat()
将任何的字符串拼接，包括null值
示例：

hrdb=> --字符串拼接函数 concat()
hrdb=> SELECT concat(‘https‘,null,‘://‘,‘www‘,‘.‘,‘google‘,‘.‘,‘com‘) AS result;
         result         
------------------------
 https://www.google.com

多个字符串拼接并按照指定的格式进行分隔函数
concat_ws()
使用第一个参数作为分隔符，将多个字符串按照第一个参数的分隔方式，将字符串分隔。null作为第一个参数来分隔字符串，将被忽略。
示例：

hrdb=> --字符串拼接分隔函数 concat_ws()
hrdb=> SELECT concat_ws(chr(9),‘Huawei‘,‘5885H‘) AS result;
    result     
---------------
 Huawei  5885H
(1 row)

转换字符串编码函数
convert(string,src_encoding,dest_encoding)
将字符串转换为不同的编码,src_encoding表示源编码，dest_encoding表示要转换的编码
示例：

hrdb=> --字符串编码转换函数
hrdb=> SELECT convert(‘postgresql‘,‘UTF8‘,‘LATIN1‘) AS result;
         result         
------------------------
 \x706f737467726573716c
(1 row)

编码和解码函数
decode() 和 encode()
将指定的字符串（或者二进制数据类型）转换为二进制数据类型（或字符串）
示例：

hrdb=> --编码解码函数 encode() decode()
hrdb=> SELECT decode(md5(‘PostgreSQL‘),‘base64‘) AS result;
                       result                       
----------------------------------------------------
 \xdfdf5b77579ee7cef6e3979c69ce9fdfd6de69af7df3ce9f
(1 row)

hrdb=> SELECT encode(‘\xdfdf5b77579ee7cef6e3979c69ce9fdfd6de69af7df3ce9f‘,‘base64‘) AS result;
              result              
----------------------------------
 399bd1ee587245ecac6f39beaa99886f
(1 row)

温馨提示：
编码解码的格式有 base64,hex,escape

格式化输出函数
format()
将字符串以类C语言的格式输出
示例：

hrdb=> --格式化输出函数 format()
hrdb=> SELECT format(‘PostgreSQL %s %2$s %3$s ‘,‘is‘,‘most‘,‘popular‘,‘database‘) AS result;
           result            
-----------------------------
 PostgreSQL is most popular 
(1 row)

返回指定位置左边的字符串
left()
返回指定位置左边的字符串，如果指定的位置是一个负数，则从右边开始截取
示例：

hrdb=> --返回指定位置字符串左边的字符函数 left()
hrdb=> SELECT left(‘https://www.baidu.com‘,5) AS result;
 result 
--------
 https
(1 row)

hrdb=> SELECT left(‘https://www.baidu.com‘,-13) AS result;
  result  
----------
 https://
(1 row)

返回指定位右边的字符串
left()
返回指定位置右边的字符串，如果指定的位置是一个负数，则从左边开始截取
示例：

hrdb=> --返回指定位置字符串右左边的字符函数 left()
hrdb=> SELECT right(‘https://www.baidu.com‘,13) AS result;
    result     
---------------
 www.baidu.com
(1 row)

hrdb=> SELECT right(‘https://www.baidu.com‘,-5) AS result;
      result      
------------------
 ://www.baidu.com

字符串填充函数
lpad()和 rpad() 表示左填充和右填充
返回超过字符串本身长度的字符将以指定的字符填充
示例：

--字符串填充函数 lpad() rpad()
--分别将employees表中的first_name列设置为右对齐
--将salary列设置为左对齐
hrdb=> SELECT lpad(first_name,15,‘-‘) as first_name,
hrdb-> rpad(salary::varchar,10,‘+‘) as salary
hrdb-> FROM employees LIMIT 2;
   first_name    |   salary   
-----------------+------------
 ---------Steven | 24000.00++
 ----------Neena | 17000.00++

指定位置字符串分隔函数
split_part()
将字符串从指定的位置进行分隔并返回指定位置分隔的字符串
示例：

hrdb=> --指定位置字符串分隔函数
hrdb=> SELECT split_part(‘https://www.baidu.com‘,‘//‘,2) AS result;
    result     
---------------
 www.baidu.com
(1 row)

以字符为单位一一替换函数
translate()
以字符为单位将字符串中的字符一一替换
示例：

hrdb=> --以字符为单位一一替换函数
hrdb=> SELECT translate(‘Postgr2e3S4QL‘,‘123456789‘,‘‘) AS result;
   result   
------------
 PostgreSQL
(1 row)

1.3字符串处理函数的实际应用案例

将字符串中的每一个字符循环打印出来。此时要涉及到字符串的遍历，大家知道在过程化语言中如Oracle中的 PL/SQL 还是 PostgreSQL中的 PL/PGSQL，要遍历字符串非常容易，使用for循环就可以遍历，但是在 SQL 语句中要实现字符串的循环输出如何做呢？

示例：将 PostgreSQL 循环遍历输出

hrdb=> SELECT substring(t1.txt,t2.id) AS col1,
hrdb->        substring(t1.txt,char_length(t1.txt) - t2.id + 1) AS col2,
hrdb->        left(t1.txt,t2.id) AS col3,
hrdb->        right(t1.txt,t2.id) AS col4
hrdb-> FROM
hrdb->   (SELECT ‘PostgreSQL‘ AS txt) t1
hrdb-> JOIN
hrdb->   (SELECT id
hrdb(>    FROM generate_series(1,10) id) t2 ON (t2.id != 0);
    col1    |    col2    |    col3    |    col4    
------------+------------+------------+------------
 PostgreSQL | L          | P          | L
 ostgreSQL  | QL         | Po         | QL
 stgreSQL   | SQL        | Pos        | SQL
 tgreSQL    | eSQL       | Post       | eSQL
 greSQL     | reSQL      | Postg      | reSQL
 reSQL      | greSQL     | Postgr     | greSQL
 eSQL       | tgreSQL    | Postgre    | tgreSQL
 SQL        | stgreSQL   | PostgreS   | stgreSQL
 QL         | ostgreSQL  | PostgreSQ  | ostgreSQL
 L          | PostgreSQL | PostgreSQL | PostgreSQL
(10 rows)

作者：宋少华

PostgreSQL分会培训认证委员会委员、晟数科技首席技术专家、晟数学院金牌讲师、oracle 11g OCM、PostgreSQL首批PGCE。

曾服务于国家电网冀北电力有限公司建设大数据平台，为人社局和北京市卫计委构建IT基础服务，为多家银行和证券公司构建web 服务器，系统及数据库维护；具有对税务局、国家电网、银行等政府行业和民营企业的IT培训经验；为相关安全行业设计DW数据仓库模型，使用PostgreSQL,Greenplum,HUAWEIGaussDB,Vertica和Clickhouse 做数据基础服务，开发TB级数据落地程序及百TB级别数据迁移程序。