1、查看是否包含子串
- 示例：查询ID包含子串'MS'的数据
-- position
SELECT id FROM my_table WHERE position('MS' IN upper(id)) > 0;
-- strpos
SELECT id FROM my_table WHERE STRPOS(upper(id), 'MS') > 0;
-- ~
SELECT id FROM my_table WHERE upper(id) ~ 'MS';
2、字符串切割为数组
- 示例：将id以字符_分割为数组
-- string_to_array
SELECT string_to_array(id, '_') FROM my_table;
3、替换子串
REPLACE(string text, from text, to text)
4、截取子串
substring(text, int4, int4)
--示例
-- 2020-11-18 15:30:14.311+08 -> 20201118153014311
UPDATE "order" SET serial_number = substring(REPLACE(REPLACE(REPLACE(REPLACE(apply_time::VARCHAR, '-', ''), ' ', ''), ':', ''), '.', ''), 1, 17)
4、拼接字符串
-- concat(any)
--示例
-- 在字段名为zip的列增加.zip的后缀
SELECT concat(zip, '.zip') FROM "my_table"
