6.3　字符串函数

字符串函数主要用来处理数据库中的字符串数据。PostgreSQL中的字符串函数有计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。本节将介绍各种字符串函数的功能和用法。

6.3.1　计算字符串字符数的函数和字符串长度的函数

CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。

【例6.21】使用CHAR_LENGTH函数计算字符串字符个数，输入语句如下：

     SELECT CHAR_LENGTH('date'), CHAR_LENGTH('egg');

语句执行后结果如图6-22所示。

图6-22　SQL语句执行结果

LENGTH(str)返回值为字符串的字节长度，使用utf8编码字符集时，一个汉字是3个字节，一个数字或字母算一个字节。

【例6.23】使用LENGTH函数计算字符串长度，输入语句如下：

     SELECT LENGTH('date'), LENGTH('egg');

语句执行后，结果如图6-23所示。

图6-23　SQL语句执行结果

可以看到，计算的结果与CHAR_LENGTH相同，因为英文字符的个数和所占的字节相同，一个字符占一个字节。

6.3.2　合并字符串函数CONCAT(s1,s2,?)、CONCAT_WS(x,s1,s2,?)

CONCAT(s1,s2,…)的返回结果为连接参数产生的字符串。若有任何一个参数为NULL，则返回值为NULL。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。

【例6.24】使用CONCAT函数连接字符串，输入语句如下：

     SELECT CONCAT('PostgreSQL', '11.2'),CONCAT('Postgre',NULL, 'SQL');

语句执行后，结果如图6-24所示。

图6-24　SQL语句执行结果

CONCAT('PostgreSQL', '11.2')返回两个字符串连接后的字符串；CONCAT('Postgre',NULL,'SQL')中有一个参数为NULL，因此合并的时候忽略不计。

在CONCAT_WS(x,s1,s2,…)中，CONCAT_WS代表CONCAT With Separator，是CONCAT()的特殊形式。第一个参数x是其他参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其他参数。如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。

【例6.25】使用CONCAT_WS函数连接带分隔符的字符串，输入语句如下：

     SELECT CONCAT_WS('-', '1st','2nd', '3rd'), CONCAT_WS('*', '1st', NULL, '3rd');

语句执行后，结果如图6-25所示。

图6-25　SQL语句执行结果

CONCAT_WS('-', '1st','2nd', '3rd')使用分隔符‘-’将3个字符串连接成一个字符串，结果为“1st-2nd-3rd”；CONCAT_WS('*', '1st', NULL, '3rd')使用分隔符‘*’将两个字符串连接成一个字符串，同时忽略NULL值。

6.3.3　获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)

LEFT(s,n)返回字符串s最左边的n个字符。

【例6.26】使用LEFT函数返回字符串中左边的字符，输入语句如下：

     SELECT LEFT('football', 5);

语句执行后，结果如图6-26所示。

图6-26　SQL语句执行结果

函数返回字符串“football”从左边开始长度为5的子字符串，结果为“footb”。

RIGHT(s,n)返回字符串s最右边的n个字符。

【例6.27】使用RIGHT函数返回字符串中右边的字符，输入语句如下：

     SELECT RIGHT('football', 4);

语句执行后，结果如图6-27所示。

图6-27　SQL语句执行结果

函数返回字符串“football”从右边开始长度为4的子字符串，结果为“ball”。

6.3.4　填充字符串的函数LPAD(s1,len,s2)和RPAD(s1,len,s2)

LPAD(s1,len,s2)返回字符串s1，其左边由字符串s2填充，填充长度为len。假如s1的长度大于len，则返回值被缩短至len字符。

【例6.28】使用LPAD函数对字符串进行填充操作，输入语句如下：

     SELECT LPAD('hello',4,'??'), LPAD('hello',10,'??');

语句执行后，结果如图6-28所示。

图6-28　SQL语句执行结果

字符串“hello”长度大于4，不需要填充，因此LPAD('hello',4,'??')只返回被缩短的长度为4的子串“hell”；字符串“hello”长度小于10，LPAD('hello',10,'??')返回结果为“?????hello”，左侧填充‘？’，长度为10。

RPAD(s1,len,s2)返回字符串s1，其右边被字符串s2填补至len字符长度。假如字符串s1的长度大于len，则返回值被缩短到与len字符相同的长度。

【例6.29】使用RPAD函数对字符串进行填充操作，输入语句如下：

     SELECT RPAD('hello',4,'?'), RPAD('hello',10,'?');

语句执行后，结果如图6-29所示。

图6-29　SQL语句执行结果

字符串“hello”长度大于4，不需要填充，因此RPAD('hello',4,'??')只返回被缩短的长度为4的子串“hell”；字符串“hello”长度小于10，RPAD('hello',10,'??')返回结果为“hello?????”，右侧填充‘？’，长度为10。

6.3.5　删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)

LTRIM(s)返回字符串s，字符串左侧空格字符被删除。

【例6.30】使用LTRIM函数删除字符串左边的空格，输入语句如下：

     SELECT '(  book  )',CONCAT('(',LTRIM('  book  '),')');

语句执行后，结果如图6-30所示。

图6-30　SQL语句执行结果

LTRIM只删除字符串左边的空格，而右边的空格不会被删除，“ book ”删除左边空格之后的结果为‘book ’。

RTRIM(s)返回字符串s，字符串右侧空格字符被删除。

【例6.31】使用RTRIM函数删除字符串右边的空格，输入语句如下：

     SELECT '(  book  )',CONCAT('(', RTRIM ('  book  '),')');

语句执行后，结果如图6-31所示。

图6-31　SQL语句执行结果

RTRIM只删除字符串右边的空格，左边的空格不会被删除，“ book ”删除右边空格之后的结果为“ book”。

TRIM(s)删除字符串s两侧的空格。

【例6.32】使用TRIM函数删除指定字符串两端的空格，输入语句如下：

     SELECT '(  book  )',CONCAT('(', TRIM('  book  '),')');

语句执行后，结果如图6-32所示。

图6-32　SQL语句执行结果

可以看到，函数执行之后字符串“ book ”两边的空格都被删除，结果为“book”。

6.3.6　删除指定字符串的函数TRIM(s1 FROM s)

TRIM(s1 FROM s)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下，删除空格。

【例6.33】使用TRIM(s1 FROM s)函数删除字符串中两端指定的字符，输入语句如下：

     SELECT TRIM('xy' FROM 'xyboxyokxyxy') ;

语句执行后，结果如图6-33所示。

图6-33　SQL语句执行结果

删除字符串“xyboxyokxyxy”两端的重复字符串“xy”，而中间的“xy”并不删除，结果为“boxyok”。

6.3.7　重复生成字符串的函数REPEAT(s,n)

REPEAT(s,n)返回一个由重复的字符串s组成的字符串，n表示重复生成的次数。若n<=0，则返回一个空字符串；若s或n为NULL，则返回NULL。

【例6.34】使用REPEAT函数重复生成相同的字符串，输入语句如下：

     SELECT REPEAT('PostgreSQL', 3);

语句执行后，结果如图6-34所示。

图6-34　SQL语句执行结果

REPEAT('PostgreSQL', 3)函数返回的字符串由3个重复的“PostgreSQL”字符串组成。

6.3.8　替换函数REPLACE(s,s1,s2)

REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。

【例6.35】使用REPLACE函数进行字符串替代操作，输入语句如下：

     SELECT REPLACE('xxx.PostgreSQL.com', 'x', 'w');

语句执行后，结果如图6-35所示。

图6-35　SQL语句执行结果

REPLACE('xxx.PostgreSQL.com', 'x', 'w')将“xxx.PostgreSQL.com”字符串中的‘x’字符替换为‘w’字符，结果为“www.PostgreSQL.com”。

6.3.9　获取子串的函数SUBSTRING(s,n,len)

SUBSTRING(s,n,len)表示从字符串s返回一个长度为len的子字符串，起始于位置n。也可能对n使用一个负值，假若这样，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符。

【例6.36】使用SUBSTRING函数获取指定位置处的子字符串，输入语句如下：

语句执行后，结果如图6-36所示。

图6-36　SQL语句执行结果

SUBSTRING('breakfast',5)返回从第5个位置开始到字符串结尾的子字符串，结果为“kfast”；SUBSTRING('breakfast',5,3)返回从第5个位置开始长度为3的子字符串，结果为“kfa”；SUBSTRING('lunch', -3)返回整个字符串。

提　示

6.3.10　匹配子串开始位置的函数POSITION(str1 IN str)

POSITION(str1 IN str)函数的作用是返回子字符串str1在字符串str中的开始位置。

【例6.37】使用POSITION函数查找字符串中指定子字符串的开始位置，输入语句如下：

     SELECT POSITION('ball'IN 'football');

语句执行后，结果如图6-37所示。

图6-37　SQL语句执行结果

子字符串“ball”在字符串“football”中从第5个字母位置开始，因此函数返回结果为5。

6.3.11　字符串逆序的函数REVERSE(s)

REVERSE(s)将字符串s反转，返回的字符串顺序和s字符串顺序相反。

【例6.38】使用REVERSE函数反转字符串，输入语句如下：

     SELECT REVERSE('abc');

语句执行后，结果如图6-38所示。

图6-38　SQL语句执行结果

由结果可以看到，字符串“abc”经过REVERSE函数处理之后所有字符串顺序被反转，结果为“cba”。