PostgreSQL字符串处理函数

coverl

前期准备，新建数据表
CREATE TABLE mytable
(
id serial NOT NULL,
"name" character varying(20),
geom geometry,
remark character varying(255)
)
插入数据
Insert into mytable(name,geom,remark) values('test1','point(12 23)','remark1');
Insert into mytable(name,geom,remark) values('test2','point(14 23)','remark2');
Insert into mytable(name,geom,remark) values('test3','point(17 23)','remark3');
Insert into mytable(name,geom,remark) values('test4','point(92 29)','remark4');
Insert into mytable(name,geom,remark) values('test5','point(82 83)','remark5');
1、 字符串连接符 '||'
例子：select name || remark as newname from mytable where name like '%test%';
结果：
newname
"test1remark1"
"test2remark2"
"test3remark3"
"test4remark4"
"test5remark5"
2、 字符串长度：bit长度bit_length(string),字符长度char_length(string)或者是character_length(string),字节长度octet(string)，具体的长度跟数据库字符编码有关，注意中文字符的长度
例子：select name,bit_length(name),char_length(name),octet_length(name) from mytable where name like '%test%';
结果
"test1";40;5;5
"test2";40;5;5
"test3";40;5;5
"test4";40;5;5
"test5";40;5;5
3、 字符串替换overlay(string placing string from int [for int])
例子：select name,overlay(name placing 'DD' from 5 for 2) from mytable where name like '%test%';
结果
"test1";"testDD"
"test2";"testDD"
"test3";"testDD"
"test4";"testDD"
"test5";"testDD"
4、 查找字符串位子position(substring in string)
例子：select name,position('test' in name) from mytable where name like '%test%';
结果：
"test1";1
"test2";1
"test3";1
"test4";1
"test5";1
5、 取子串，substring(string [from int] [for int]),substring(string from pattern)
例子1：select name,substring(name from 1 for 2) from mytable where name like '%test%';
结果1：
"test1";"te"
"test2";"te"
"test3";"te"
"test4";"te"
"test5";"te"
例子2：select name,substring(name from '^...') from mytable where name like '%test%';
结果2：以任何三个字符开头的，以任何两个字符结尾的为：substring(name from '..$')
"test1";"tes"
"test2";"tes"
"test3";"tes"
"test4";"tes"
"test5";"tes"
6、 转换为大些和小写：upper(string)和lower(string)
例子：select name,upper(name),lower(name) from mytable where name like '%test%';
结果：
"test1";"TEST1";"test1"
"test2";"TEST2";"test2"
"test3";"TEST3";"test3"
"test4";"TEST4";"test4"
"test5";"TEST5";"test5"
7、 去除开头或者结尾或者全部的包含的字符串trim([leading|trailing|both] [characters] from string)
例子：select name,trim(name),trim(leading 'test' from name),trim('1' from name) from mytable where name like '%test%';
结果：
"test1";"test1";"1";"test"
"test2";"test2";"2";"test2"
"test3";"test3";"3";"test3"
"test4";"test4";"4";"test4"
"test5";"test5";"5";"test5"