PostgreSQL 字符串处理与日期处理
原地址
PostgreSQL学习手册(函数和操作符<二>)
字符串长度、大小写
SELECT CHAR_LENGTH('test')
SELECT LENGTH('test')
LENGTH(string,encoding name)
SELECT LENGTH('测试','UTF-8');
LOWER(string) 或者 UPPER(string)
ASCII(string)
SELECT ASCII('abc')
字符串格式化
FORMAT(formatstr text [,formatarg "any" [, ...] ])
字符串拼接
SELECT 'number' || 123
CONCAT(str "any" [, str "any" [, ...] ])
CONCAT_WS(sep text, str "any" [,str "any" [, ...] ])
SELECT * FROM CONCAT_WS('#','hello','world')
字符串剪切与截取
LPAD(string text, length int [,fill text])
RPAD(string text, length int [,fill text])
SELECT LPAD('12345', 10,'0')
TRIM([leading | trailing | both] [characters] from string)
SELECT TRIM(both ' ' from ' hello world')
BTRIM(string text [, characters text])
RTRIM(string text [, characterstext])
LTRIM(string text [, characterstext])
SELECT BTRIM('yyhello worldyyyy','y')
LEFT(str text, n int)
RIGHT(str text, n int)
SUBSTRING(string from int [for int])
SELECT SUBSTRING('hello world' from 7 for 5)
字符串加引号
QUOTE_IDENT(string text)
QUOTE_LITERAL(STRING TEXT)
QUOTE_LITERAL(value anyelement)
SELECT 'l''host"' -- 结果是'l'host"'
SELECT QUOTE_LITERAL('l''host"') -- 结果是'l''host"'
字符串分割
SPLIT_PART(string text,delimiter text, field int)
REGEXP_SPLIT_TO_ARRAY(stringtext, pattern text [, flags text])
REGEXP_SPLIT_TO_TABLE(stringtext, pattern text [, flagstext])
SELECT SPLIT_PART('hello#world','#',2)
SELECT REGEXP_SPLIT_TO_ARRAY('hello#world','#')
SELECT REGEXP_SPLIT_TO_TABLE('hello#world','#') as split_res
字符串查找、反转与替换
POSITION(substring in string)
SELECT POSITION('h' in 'hello world')
REVERSE(str)
REPEAT(string text, number int)
REPLACE(string,string,string)
SELECT REPLACE('hello world',' ','#')
REGEXP_MATCHES(string text,pattern text [, flags text])
REGEXP_REPLACE(string text,pattern text,replacement text[, flags text])
SELECT REGEXP_MATCHES('hello world','.o.','g')
SELECT REGEXP_MATCHES('hello world','.o.')
时间处理
SELECT TO_CHAR(TO_TIMESTAMP(CREATE_TIME),'YYYY-MM-DD HH24:MI:SS')
SELECT EXTRACT(YEAR FROM NOW());