PostgreSQL字符串处理函数

IT程序员开发必备-各类资源下载清单，史上最全IT资源，个人收藏总结！

前期准备，新建数据表

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"