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….，我们不能一个一个去数它的位置在哪里，如果字符串短，且容易数，就没有必要去使用 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)