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' ) -- 结果是'a'的ascii码

FORMAT(formatstr text [,formatarg "any" [, ...] ]) -- 类似于printf

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' ) -- 结果 "0000012345"

TRIM([leading | trailing | both] [characters] from string)

SELECT TRIM(both ' ' from ' hello world' ) -- 结果是'hello world'

BTRIM(string text [, characters text])

RTRIM(string text [, characterstext])

LTRIM(string text [, characterstext])

SELECT BTRIM( 'yyhello worldyyyy' , 'y' ) -- 结果是'hello world'

LEFT (str text, n int ) -- 返回字符串前n个字符，n为负数时返回除最后|n|个字符以外的所有字符

RIGHT (str text, n int )

SUBSTRING (string from int [ for int ])

SELECT SUBSTRING ( 'hello world' from 7 for 5) -- 结果是'world'

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) -- 结果是'world'

SELECT REGEXP_SPLIT_TO_ARRAY( 'hello#world' , '#' ) -- 结果是{hello,world}

SELECT REGEXP_SPLIT_TO_TABLE( 'hello#world' , '#' ) as split_res -- 结果是两行,第一行hello,第二行world

POSITION( substring in string) -- 查找

SELECT POSITION( 'h' in 'hello world' ) -- 结果是1，这里从1开始计数

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' ) -- 返回两行，第一行是'lo ',第二行是'wor'

SELECT REGEXP_MATCHES( 'hello world' , '.o.' ) -- 返回第一个匹配，'lo '

SELECT TO_CHAR(TO_TIMESTAMP(CREATE_TIME), 'YYYY-MM-DD HH24:MI:SS' )

SELECT EXTRACT( YEAR FROM NOW());

AND to_timestamp(a.upload_time, 'yyyy-MM-dd' )>= '"+startTime+"' and to_timestamp(a.upload_time, 'yyyy-MM-dd' ) <= '"+endTime+"'

substring ( '2019-04-08 14:18:09' , index ,k):

SELECT substring (upload_time,1,10) from table WHERE upload_time= '2019-04-08 14:18:09'

SELECT substring (upload_time,12,5) from table WHERE upload_time= '2019-04-08 14:18:09'