postgresql--与字符串相关的函数和操作符
楔子
我们在筛选数据的时候，很多时候要对字符串进行一些处理，下面来看看postgresql支持哪些字符串的操作吧
数据集如下
select * from t;
支持字符串操作的函数或者操作符
||
将多个字符串进行拼接
-- 需要注意的是，在pgsql中，所有的字符串都必须用单引号，双引号的话会被解释为字段 -- 为什么这么设计，是因为在pgsql中存在着大小写的问题，不管你查询的时候字段是大写还是小写，pgsql都会给你转成小写 -- 可如果这个时候表中的字段是大写的话，那么就会报出字段不存在的错误，因为pgsql给你转换成了小写 -- 解决的办法就是给查询的字段加上双引号，这个时候就不会给你转成小写了 -- 因此需要注意，双引号表示字段，单引号表示字符串 select 'my' || ' name' || ' is' || ' satori'; /* my name is satori */ -- 输出结果如上，另外在输出的时候，我就不写字段名了 -- 我们看到自动把所有的字符串都拼接起来了 select 'my' || ' age' || ' is ' || 16; /* my age is 16 */ -- 我们看到，即便出现了数字，也会隐式的将数字转化为字符串 -- 因此16会被转化为'16' -- 下面这句sql会输出什么呢？ select 15 || 16; -- 很不幸，这是会报错的，会提示: "错误: 操作符不存在: integer || integer 建议：没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换." -- 如果是||，两边不能全部都为数字，否则会报错 -- 对于||来说，pgsql要求||两边至少有一个元素要是字符串格式 select '1' || 2 || 3 || 4; /* 1234 */ -- 此时转化成功了，这是为什么呢？ -- 我们刚才说||要求两边不能全部是数字啊。 -- 这是因为多个||是按照从左到右的顺序进行拼接的 -- 对于第一个||，左边是字符串'1'，那么也会把2转化为'2'，得到的结果就是'12' -- 对于第二个||，进行拼接的时候，左边的内容已经不是数字2了，而是变成了字符串'12'，所以会把3变成'3'，得到的结果就是'123' -- 同理对于第三个||也是如此，因此最后结果是'1234' select '1' + '2'; -- 这种操作也是不行的，因为+这种操作符是适用于整型、浮点型的 -- 因此上面的结果是不会进行拼接的，拼接需要使用||，或者后面介绍的concat select '1'::int + '2'::int; /* 3 */ -- 我们如果是想当成数字运算的话，需要进行转换，在pgsql中，转换的方式可以使用 column::type、'xxx'::type -- 另外如果含有NULL，那么得到的结果也是NULL select 'a' || 'b' || null; /* NULL */
concat
将多个字符串进行拼接
select concat('my', ' name', ' is', ' satori');
/*
my name is satori
*/
-- concat是一个函数，用于将多个字符拼接起来
-- 那么它和之间介绍的||之间有什么区别呢？
select concat(1, 2, 3, 4);
/*
my name is satori
*/
-- 因此看到区别了吧，对于concat来说，即使出现了数字也无所谓，会统统转成字符串
select concat('a', 'b', null);
/*
ab
*/
-- 如果含有NULL值，那么NULL会被忽略掉，这也是和||操作符之间的一个区别bit_length
查看一个字符串占多少位
select bit_length('a');
/*
8
*/
-- 一个英文字符或者数字占一个字节，8位
select bit_length('爱');
/*
24
*/
-- 汉字，则是3字节
select bit_length('爱a1');
/*
40
*/
-- 组合起来，依旧是汉字占3个字节，英文字符占1个字节octet_length
查看一个字符串占多少个字节
select octet_length('a');
/*
1
*/
select octet_length('灰原哀123');
/*
12
*/
-- 汉字占3个，数字占1个char_length
查看一个字符串的长度，注意是长度
select char_length('灰原ai');
/*
4
*/
-- char_length是计算字符长度的，个人觉得这个比bit_length和octet_length要常用很多lower、upper
将一个字符串转成小写、大写
select lower('My Lover'), upper('My Lover');
/*
my lover MY LOVER
*/initcap
将字符串中单词的首字母大写，其余转成小写
select initcap('my NamE iS SATORI');
/*
My Name Is Satori
*/substring
提取字符串的指定部分
select substring('hello', 1, 3);
/*
hel
*/
-- substring(str, start, count)
-- 表示从第start个字符串开始取，取count个。注意：第几个，是从1开始的，不是0
-- 所以substring('hello', 1, 3)，表示从第1个字符h开始取，取3个，所以是hel
-- 另外这个substring还支持正则，这个正则我们后面介绍
select substring('hello', 2);
/*
ello
*/
-- 不指定取多少个，默认取到结尾position
查找字符串中的某个子串的位置
select position('at' in 'satori');
/*
2
*/
-- 注意结果是2，说明索引从1开始，a是第2个
select position('xxx' in 'satori');
/*
0
*/
-- 不存在话返回0
-- 提取出hello the cruel world中的cruel world，不允许使用数数的方式
select substring('hello the cruel world', position('cruel' in 'hello the cruel world'))
/*
cruel world
*/
-- 两者就结合起来了overlay
替换指定位置的字符串
select overlay('abcdef' placing '嘎嘎嘎' from 2 for 4);
/*
a嘎嘎嘎f
*/
-- 表示把字符串'abcdef'的第2个字符开始的后4个字符替换为'嘎嘎嘎'
-- from 2表示从第2个字符开始，这里是b，for 4表示数4个，所以from 2 for 4表示的就是bcde
-- 然后placing为'嘎嘎嘎'，我们发现我们要换4个，但是却传入了3个字符，但是不影响
-- 比如我们想把第2个开始的字符之后的4个字符删掉就可以这么做
select overlay('abcdef' placing '' from 2 for 4);
/*
af
*/
select overlay('abcdef' placing 'xxxxxxxxxxxxxxxxxxx' from 2 for 4);
/*
axxxxxxxxxxxxxxxxxxxf
*/
-- 即使传入的字符个数比要替换的字符个数多，也不影响
select overlay('abcdef' placing '嘎嘎嘎' from 2);
/*
a嘎嘎嘎ef
*/
-- 如果不指定for，也就是不指定替换几个，那么传入的替换字符有几个，就替换几个
-- 我们从第2个开始，而我们传入的'嘎嘎嘎'有三个字符，所以替换3个
-- 操作一波，将字符串'hello cruel world'中间的cruel换成beautiful
select overlay('hello cruel world' placing 'beautiful' from position('cruel' in 'hello cruel world') for char_length('cruel'))
/*
hello beautiful world
*/replace
替换指定的字符串
select replace('aabbccddcceec', 'cc', '哈哈')
/*
aabb哈哈dd哈哈eec
*/
-- 将所有的'cc'换成'哈哈'trim
从字符串的开头/结尾/两端开始将字符删掉，类似于python中的strip
select trim(leading 'abc' from 'abcdefg'); /* defg */ -- 从字符串'abcdefg'的左边开始一个字符一个字符的遍历，只要在'abc'当中，那么就剥掉 -- 遇见一个不在的，那么就立即停止 select trim(trailing 'eg' from 'abcdefg'); /* abcdef */ -- 从右边开始，g在'eg'里面，剥掉，f不在，所以停止 select trim(both 'abceg' from 'abcdefg'); /* def */ -- 不用想了，从两端开始
ascii
返回字符串中第一个字符的ASCII码
select ascii('gaga');
/*
103
*/
select ascii('g');
/*
103
*/
-- 返回参数的第一个字符的ASCII码
select ascii('嘎嘎嘎');
/*
22030
*/chr
将一个ascii码转成对应的字符
select chr(97);
/*
a
*/
select ascii('嘎');
/*
22030
*/
select chr(22030);
/*
嘎
*/concat_ws
将多个字符用指定的分隔符拼接起来
select concat('a', 'b', 'c');
/*
abc
*/
select concat_ws('--', 'a', 'b', 'c');
/*
a--b--c
*/left
返回字符串的前n个字符
select left('satori', 3);
/*
sat
*/
-- 返回前3个字符
select left('satori', 30);
/*
satori
*/
-- 指定的个数超过了字符总个数，返回全部
select left('satori', -2);
/*
sato
*/
-- 如果指定个数n小于0，那么返回除了最后abs(n)个之外的所有字符
-- 这里是-2，返回最后两个之外的所有字符
select left('satori', -20) = '';
/*
t
*/
-- 显然这里就为空字符串了right
返回字符串的后n个字符
select right('satori', 3);
/*
ori
*/
-- 返回后3个字符
select right('satori', 30);
/*
satori
*/
-- 指定的个数超过了字符总个数，返回全部
select right('satori', -2);
/*
tori
*/
-- 如果指定个数n小于0，那么返回除了最前abs(n)个之外的所有字符
-- 这里是-2，返回最前两个之外的所有字符
select right('satori', -20) = '';
/*
t
*/
-- 显然这里就为空字符串了lpad
向左填充指定的字符串
select lpad('satori', 10, '0');
/*
0000satori
*/
-- 填充指定的字符'0'，使得原来的字符串的个数达到10
select lpad('satori', 11, '01');
/*
01010satori
*/
-- 还可以指定一个字符串
select lpad('satori', 3, '01');
/*
sat
*/
-- 原来的字符串有6个，但是我们要填充到3个，所以直接截断
-- 相当于从左往右取3个rpad
向右填充指定的字符串
select rpad('satori', 10, '0');
/*
satori0000
*/
select rpad('satori', 11, '01');
/*
satori01010
*/
select rpad('satori', 3, '01');
/*
sat
*/
-- 我们发现对于rpad，如果指定的长度比原来的字符串的长度小
-- 那么依旧是从左开始取md5
计算一个字符串的hash值
select md5('嘎嘎嘎');
/*
27c75ea642dbd8d310e2903c3df91b84
*/reverse
翻转字符串
select reverse('abc');
/*
cba
*/repeat
重复字符串
select repeat('abc', 3);
/*
abcabcabc
*/translate
对应字符替换
select translate('satori', 'ar', '12');
/*
s1to2i
*/
-- 'a'会被替换为'1'，'r'会被替换为'2'
select translate('satori', 'ari', '12');
/*
s1to2
*/
-- ari比12长，但是a依旧对应1，r对应2，i则对应空字符串format
类似于C语言中的printf，可以格式化打印
select format('name is %s, age is %s', 'satori', 16);
/*
name is satori, age is 16
*/
-- 这里数字也要使用%s，输出百分号则是%%
select age from t limit 5;
/*
44
54
45
39
46
*/
select format('age is %s', age) from t limit 5;
/*
age is 44
age is 54
age is 45
age is 39
age is 46
*/
-- 即便我们从表中筛选字段的时候，也是可以用的
-- 会将表中的每一个字段都进行format
-- 不仅仅是format,concat、||等基本上任何函数都支持
-- 如果是表的字段，那么会将字段中的每一个记录都会进行相同的操作
select age || '岁' from t limit 5;
/*
44岁
54岁
45岁
39岁
46岁
*/
--format还支持其他的操作
select format('|%8s|', 'haha');
/*
| haha|
*/
-- %8s表示替换之后，要占满8位，不够的话，用空字符在左边填充
select format('|%-8s|', 'haha');
/*
|haha |
*/
-- %-8s，和%8s类似，只不过是在右边填充空字符
select format('|%3s|', 'hahaha');
/*
|hahaha|
*/
-- 如果不够的话也不影响，不会影响后面的字符
-- %3s表示要填满3位，但是'hahaha'有6个字符，但是不影响，不会截断