| 求字符串长度 |
LENGTH(str) |
LENGTH(str) |
LEN(str) |
LENGTH(str) |
| 字符切割 |
SUBSTR(str,index,length) |
SUBSTR(str,index,length) |
SUBSTRING(str,index,length) |
SUBSTRING(str,index,length) |
| 字符串连接 |
str1||str2||str3...strN |
str1||str2||str3...strN |
str1+str2+str3...strN |
CONCAT(str1,str2,str3...strN) |
| 字符串索引 |
INSTR(str,subStr) |
strpos(str,subStr) |
CHARINDEX(subStr,str) |
INSTR(str,subStr) |
| 日期格式化 |
TO_CHAR(date,'yyyy-mm-dd hh24:mi:ss') |
TO_CHAR(date::timestamp,'yyyy-mm-dd hh24:mi:ss') |
CONVERT(VARCHAR,date,20) |
DATE_FORMAT(date,'%Y-%m-%d %H:%i:%s') |
| 相等判断 |
decode(arg[0],arg[1],arg[2],arg[3]) |
CASE WHEN arg[0]=arg[1] THEN arg[2] ELSE arg[3] END |
CASE WHEN arg[0]=arg[1] THEN arg[2] ELSE arg[3] END |
IF(arg[0]=arg[1],arg[2],arg[3]) |
| 数字转字符 |
TO_CHAR(num) |
CAST(num AS VARCHAR) |
CAST(num AS VARCHAR) |
CONVERT(num,CHAR) |
| 判空 |
NVL(str1,str2) |
COALESCE(str1,str2) |
ISNULL(str1,str2) |
IFNULL(str1,str2) |
| 位与运算 |
BITAND(num1, num2) |
num1 & num2 |
num1 & num2 |
num1 & num2 |
| 位或运算 |
num1 + num2 - BITAND(num1, num2) |
num1 | num2 |
num1 | num2 |
num1 | num2 |
| 数字对应的ASCII |
CHR(num) |
CHR(num) |
CHAR(num) |
CHAR(num USING ASCII) |
| 字符串左填充 |
LPAD(source,padded_length,pad_string) |
LPAD(source,padded_length,pad_string) |
CONCAT(REPLICATE(pad_str, len-LEN(str)),str) |
LPAD(source,padded_length,pad_string) |
| 获取当前日期 |
SYSDATE |
CURRENT_TIMESTAMP |
GETDATE() |
SYSDATE() |
| 字符串两端添加逗号 |
,||str||, |
,||str||, |
,+str+, |
CONCAT(',',str,',') |
| 判断字符串是否为空 |
str IS NULL |
str='' |
str='' |
str='' |
| 字符串转化为日期 |
TO_DATE(str,'yyyymmdd') |
TO_DATE(str,'yyyymmdd') |
CONVERT(DATE,str,112) |
STR_TO_DATE(str,'%Y%m%d') |
| 字符串转整数 |
TO_NUMBER(str) |
CAST(str AS DECIMAL) |
CAST(str AS DECIMAL) |
CONVERT(str,DECIMAL) |
| 字符串转化为日期时间 |
TO_TIMESTAMP(dateStr||' '||timeStr,'yyyyMMdd HH24:mi:ss') |
TO_TIMESTAMP(...) |
CONVERT(DATETIME,CONCAT(CONVERT(DATE,dateStr,112),' ',timeStr),120) |
STR_TO_DATE(CONCAT(dateStr,' ',timeStr),'%Y%m%d %H:%i:%s') |
| 当前日期（整型） |
TO_NUMBER(TO_CHAR(SYSDATE,'yyyymmdd')) |
CAST(TO_CHAR(CURRENT_TIMESTAMP,'yyyymmdd') AS INTEGER) |
CAST(CONVERT(VARCHAR,GETDATE(),112) AS INT) |
CONVERT(DATE_FORMAT(SYSDATE(),'%Y%m%d'),DECIMAL) |
| 数字转化为时间间隔 |
TRUNC(num/86400) || '天' || TRUNC(MOD(num,86400)/3600) || '小时' || TRUNC(MOD(num,3600)/60) || '分' || MOD(num,60) || '秒' |
同Oracle |
CONCAT(FLOOR(num/86400),'天',FLOOR((num%86400)/3600),'小时',FLOOR((num%3600)/60),'分',num%60,'秒') |
CONCAT(FLOOR(num/86400),'天',FLOOR(MOD(num,86400)/3600),'小时',FLOOR(MOD(num,3600)/60),'分',MOD(num,60),'秒') |
| 日期延后若干天 |
args[0] + args[1] |
`args[0] + (args[1] |
|
’ day’)::INTERVAL` |
| 日期延后若干月 |
ADD_MONTHS(args[0],args[1]) |
`args[0] + (args[1] |
|
’ month’)::INTERVAL` |
| 判断是否不相等 |
CASE WHEN arg[0]!=arg[1] THEN arg[2] ELSE arg[3] END |
同Oracle |
同Oracle |
IF(arg[0]!=arg[1],arg[2],arg[3]) |
| 日期相减获取天数 |
args[0] - args[1] |
EXTRACT(DAY FROM (args[0]-args[1])) |
DATEDIFF(DAY,args[1],args[0]) |
DATEDIFF(args[0],args[1]) |
| 空字符串检查并赋值 |
CASE WHEN (str1 IS NULL OR str1='') THEN str2 ELSE str1 END |
同Oracle |
同Oracle |
IF(str1 IS NULL OR str1='', str2, str1) |
| 判断字符串为NULL或空 |
str IS NULL |
(str IS NULL OR str='') |
同PostgreSQL |
同PostgreSQL |
| 判断字符串非NULL且非空 |
str IS NOT NULL |
(str IS NOT NULL AND str!='') |
同PostgreSQL |
同PostgreSQL |