5.2. 内置函数
本节主要介绍统一SQL支持原生Oracle数据库中的函数
使用时注意事项参考： 注意事项
5.2.1. 数字函数
5.2.1.1. ABS
- 语法
ABS(numeric_expression)- 描述
- 该函数返回 numeric_expression 的绝对值。ABS 将负值更改为正值，对零或正值没有影响。
参数解释
参数 |
说明 |
|---|---|
numeric_expression |
精确数值或近似数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）的表达式 |
示例
-- 转换前Oracle SQL：
SELECT ABS(-1.0), ABS(0), ABS(6.66), ABS(3-6) FROM DUAL;
ABS(-1.0)|ABS(0)|ABS(6.66)|ABS(3-6)|
---------+------+---------+--------+
1| 0| 6.66| 3|
-- 转换后TDSQL-MySQL：
SELECT ABS(-1.0), ABS(0), ABS(6.66), ABS(3-6) FROM DUAL;
ABS(-1.0)|ABS(0)|ABS(6.66)|ABS(3-6)|
---------+------+---------+--------+
1.0| 0| 6.66| 3|
5.2.1.2. BITAND
- 语法
BITAND (expr1,expr2)- 描述
- 该函数将其输入参数进行二进制按位与操作
参数解释
参数 |
说明 |
|
|---|---|---|
expr1 |
参数1 |
NUMBER 类型表达式 |
expr2 |
参数2 |
NUMBER 类型表达式 |
示例
-- 转换前Oracle SQL：
SELECT BITAND(0,0),BITAND(0,-1),BITAND(0,NULL),BITAND(1,2) FROM DUAL;
BITAND(0,0)|BITAND(0,-1)|BITAND(0,NULL)|BITAND(1,2)|
-----------+------------+--------------+-----------+
0| 0| | 0|
-- 转换后TDSQL-MySQL：
SELECT 0&0,0&-1,0&NULL,1&2 FROM DUAL;
0&0|0&-1|0&NULL|1&2|
---+----+------+---+
0| 0| | 0|
5.2.1.3. CEIL
- 语法
CEIL (numeric_expression)- 描述
- 该函数返回大于等于 numeric_expression 的最小整数
参数解释
参数 |
说明 |
|---|---|
numeric_expression |
精确数值或近似数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）的表达式 |
示例
-- 转换前Oracle SQL：
SELECT CEIL(-1.5),CEIL(1.5),CEIL(2),CEIL(6-9.5) FROM DUAL;
CEIL(-1.5)|CEIL(1.5)|CEIL(2)|CEIL(6-9.5)|
----------+---------+-------+-----------+
-1| 2| 2| -3|
-- 转换后TDSQL-MySQL：
SELECT CEIL(-1.5),CEIL(1.5),CEIL(2),CEIL(6-9.5) FROM DUAL;
CEIL(-1.5)|CEIL(1.5)|CEIL(2)|CEIL(6-9.5)|
----------+---------+-------+-----------+
-1| 2| 2| -3|
5.2.1.4. DBMS_RANDOM.VALUE
- 语法
DBMS_RANDOM.VALUE- 描述
- 返回一个随机码，此随机码需满足大于或等于 0，小于 1
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT DBMS_RANDOM.VALUE from dual;
VALUE |
----------------------------------------+
0.90603062118926722027812535155007101231|
-- 转换后TDSQL-MySQL：
SELECT rand() FROM dual;
rand() |
------------------+
0.2516990529854773|
5.2.1.5. DBMS_RANDOM.RANDOM
- 语法
DBMS_RANDOM.RANDOM- 描述
- 返回一个在 [-2^31，2^31）范围区间内的随机整数
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT DBMS_RANDOM.RANDOM from dual;
RANDOM |
----------+
-886930169|
-- 转换后TDSQL-MySQL：
SELECT floor(power(-2, 31)+rand()*power(2, 32)) FROM dual;
floor(power(-2, 31)+rand()*power(2, 32))|
----------------------------------------+
-278630359|
5.2.1.6. EXP
- 语法
EXP (numeric_expression)- 描述
- 该函数是以自然常数 e 为底的指数函数，用于返回 e 的 numeric_expression 次方
参数解释
参数 |
说明 |
|---|---|
numeric_expression |
精确数值或近似数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）的表达式 |
示例
-- 转换前Oracle SQL：
SELECT EXP(1),EXP(0),EXP(NULL),EXP(2) FROM DUAL;
EXP(1) |EXP(0)|EXP(NULL)|EXP(2) |
----------------------------------------+------+---------+---------------------------------------+
2.71828182845904523536028747135266249776| 1| |7.3890560989306502272304274605750078132|
-- 转换后TDSQL-MySQL：
SELECT EXP(1),EXP(0),EXP(NULL),EXP(2) FROM DUAL;
EXP(1) |EXP(0)|EXP(NULL)|EXP(2) |
-----------------+------+---------+----------------+
2.718281828459045| 1.0| |7.38905609893065|
5.2.1.7. FLOOR
- 语法
FLOOR (numeric_expression)- 描述
- 该函数返回小于等于数值 numeric_expression 的最大整数
参数解释
参数 |
说明 |
|---|---|
numeric_expression |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）或可以隐式转换为数值数据类型的参数 |
示例
-- 转换前Oracle SQL：
SELECT floor(0) AS "floor(0)",floor(10) AS "floor(10)",floor(10.11) AS "floor(10.11)",floor(-10.11) AS "floor(-10.11)" from dual;
floor(0)|floor(10)|floor(10.11)|floor(-10.11)|
--------+---------+------------+-------------+
0| 10| 10| -11|
-- 转换后TDSQL-MySQL
SELECT floor(0) AS `floor(0)`,floor(10) AS `floor(10)`,floor(10.11) AS `floor(10.11)`,floor(-10.11) AS `floor(-10.11)` FROM dual;
floor(0)|floor(10)|floor(10.11)|floor(-10.11)|
--------+---------+------------+-------------+
0| 10| 10| -11|
5.2.1.8. LN
- 语法
LN (numeric_expression)- 描述
- 该函数返回以 e 为底 numeric_expression 的对数
参数解释
参数 |
说明 |
|---|---|
numeric_expression |
大于 0 的数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）或可以隐式转换为数值数据类型的参数 |
示例
-- 转换前Oracle SQL：
SELECT LN(3.0) AS "Natural Logarithm" from dual;
Natural Logarithm |
----------------------------------------+
1.09861228866810969139524523692252570465|
-- 转换后TDSQL-MySQL
SELECT LN(3.0) AS `Natural Logarithm` FROM dual;
Natural Logarithm |
------------------+
1.0986122886681098|
5.2.1.9. POWER
- 语法
POWER (x , y)- 描述
- 该函数作用是返回指定数字的乘幂
参数解释
参数 |
说明 |
|---|---|
x |
表示底数，属于数值类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）表达式 |
y |
表示指数，属于数值类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）表达式。如果 x 是负数，则 y 必须是整数 |
示例
-- 转换前Oracle SQL：
select power(2,3),POWER(3,2) "Raised" from dual;
POWER(2,3)|Raised|
----------+------+
8| 9|
-- 转换后TDSQL-MySQL
SELECT power(2, 3),POWER(3, 2) AS `Raised` FROM dual;
power(2, 3)|Raised|
-----------+------+
8.0| 9.0|
5.2.1.10. SIGN
- 语法
SIGN (numeric_expression)- 描述
- 该函数返回输入数值的符号。符号为 1、-1 和 0
参数解释
参数 |
说明 |
|---|---|
numeric_expression |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）或可以隐式转换为数值数据类型的参数 |
- 返回类型
如果 numeric_expression > 0，则返回 1
如果 numeric_expression < 0，则返回 -1
如果 numeric_expression = 0，则返回 0
示例
-- 转换前Oracle SQL：
select sign(20),SIGN(0),SIGN(-1),SIGN(-15) AS "sign" from dual;
sign | sign | sign | sign
------+------+------+------
1 | 0 | -1 | -1
-- 转换后TDSQL-MySQL
SELECT sign(20),SIGN(0),SIGN(-1),SIGN(-15) AS `sign` FROM dual;
sign(20)|SIGN(0)|SIGN(-1)|sign|
--------+-------+--------+----+
1| 0| -1| -1|
5.2.1.11. SQRT
- 语法
SQRT (numeric_expression)- 描述
- 该函数返回参数 numeric_expression 的平方根
参数解释
参数 |
说明 |
|---|---|
numeric_expression |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）或可以隐式转换为数值数据类型且大于等于零的参数 |
示例
-- 转换前Oracle SQL：
SELECT sqrt(20),sqrt(0),sqrt(1),sqrt(2),sqrt(NULL) FROM DUAL;
SQRT(20) |SQRT(0)|SQRT(1)|SQRT(2) |SQRT(NULL)|
----------------------------------------+-------+-------+----------------------------------------+----------+
4.47213595499957939281834733746255247088| 0| 1|1.41421356237309504880168872420969807857| |
-- 转换后TDSQL-MySQL：
SELECT sqrt(20),sqrt(0),sqrt(1),sqrt(2),sqrt(NULL) FROM DUAL;
sqrt(20) |sqrt(0)|sqrt(1)|sqrt(2) |sqrt(NULL)|
----------------+-------+-------+------------------+----------+
4.47213595499958| 0.0| 1.0|1.4142135623730951| |
5.2.1.12. MOD
- 语法
MOD(N,M)- 描述
- 取余函数
参数解释
参数 |
说明 |
|---|---|
N |
被除数 |
M |
除数 |
示例
-- 转换前Oracle SQL：
SELECT MOD(11,4) "Modulus",MOD(11,4),MOD(11,-4),MOD(-11,4),MOD(-11,-4) FROM DUAL;
Modulus | mod | mod | mod | mod
---------+-----+-----+-----+-----
3 | 3 | 3 | -3 | -3
-- 转换后TDSQL-MySQL：
SELECT if(concat('a', 4)='a0', 11, MOD(11, 4)) AS `Modulus`,if(concat('a', 4)='a0', 11, MOD(11, 4)),if(concat('a', -4)='a0', 11, MOD(11, -4)),if(concat('a', 4)='a0', -11, MOD(-11, 4)),if(concat('a', -4)='a0', -11, MOD(-11, -4)) FROM DUAL;
Modulus|if(concat('a', 4)='a0', 11, MOD(11, 4))|if(concat('a', -4)='a0', 11, MOD(11, -4))|if(concat('a', 4)='a0', -11, MOD(-11, 4))|if(concat('a', -4)='a0', -11, MOD(-11, -4))|
-------+---------------------------------------+-----------------------------------------+-----------------------------------------+-------------------------------------------+
3| 3| 3| -3| -3|
5.2.1.13. ROUND
语法
ROUND(X)
ROUND(X,D)
- 描述
- 返回一个数值，四舍五入到指定的长度或精度
参数解释
参数 |
说明 |
|---|---|
X |
要操作的数值 |
D |
返回的长度或精度 |
示例
-- 转换前Oracle SQL：
SELECT ROUND(3, 10),ROUND(15.193,1),ROUND(15.193,-1),ROUND(0,10),ROUND(-3,10),ROUND(10) from dual;
round | round | round | round | round | round
--------------+-------+-------+--------------+---------------+-------
3.0000000000 | 15.2 | 20 | 0.0000000000 | -3.0000000000 | 10
-- 转换后TDSQL-MySQL
SELECT round(3, 10),round(15.193, 1),round(15.193, -1),round(0, 10),round(-3, 10),ROUND(10) FROM dual;
round(3, 10)|round(15.193, 1)|round(15.193, -1)|round(0, 10)|round(-3, 10)|ROUND(10)|
------------+----------------+-----------------+------------+-------------+---------+
3| 15.2| 20| 0| -3| 10|
5.2.1.14. TRUNC(number)
- 语法
TRUNC (numeric[,precision])- 描述
- 该函数返回参数 numeric 按精度 precision 截取后的值
参数解释
参数 |
说明 |
|---|---|
numeric |
表示被截取的数字 |
precision |
表示精度，为可选项，默认值为 0 |
示例
-- 转换前Oracle SQL：
SELECT TRUNC(555.666,2.2),TRUNC(555.666,-2),TRUNC(555.666) FROM DUAL;
------------------+-----------------+--------------+
TRUNC(555.666,2.2)|TRUNC(555.666,-2)|TRUNC(555.666)|
------------------+-----------------+--------------+
555.66| 500| 555|
-- 转换后TDSQL-MySQL：
SELECT truncate(555.666, 2.2),truncate(555.666, -2),truncate(555.666, 0) FROM DUAL;
truncate(555.666, 2.2)|truncate(555.666, -2)|truncate(555.666, 0)|
----------------------+---------------------+--------------------+
555.66| 500| 555|
5.2.2. 字符串函数
5.2.2.1. CHR
- 语法
CHR(n)- 描述
- 该函数根据数字参数返回字符，返回值与当前系统的字符集相关
参数解释
参数 |
说明 |
|---|---|
n |
字符的数字代码,是一个整数值，范围为 [32，127] |
示例
-- 转换前Oracle SQL：
select CHR(67),CHR(68) from dual;
CHR(67)|CHR(68)|
-------+-------+
C |D |
-- 转换后TDSQL-MySQL：
SELECT char(67),char(68) FROM dual;
char(67)|char(68)|
--------+--------+
C |D |
5.2.2.2. CONCAT
- 语法
CONCAT(char1,char2)- 描述
- 该函数用于连接两个字符串
参数解释
参数 |
说明 |
|---|---|
char1 |
字符串，字符串类型可为 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB |
char2 |
字符串，字符串类型可为 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB |
示例
-- 转换前Oracle SQL：
SELECT CONCAT('Hello',', world!') FROM DUAL;
CONCAT('HELLO',',WORLD!')|
-------------------------+
Hello, world! |
-- 转换后TDSQL-MySQL：
SELECT CONCAT('Hello', ', world!') FROM DUAL;
CONCAT('Hello', ', world!')|
---------------------------+
Hello, world! |
5.2.2.3. LOWER
- 语法
LOWER(char)- 描述
- 该函数将字符串中英文字母全部转为小写
参数解释
参数 |
说明 |
|---|---|
char |
字符串，字符串类型可为 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB |
示例
-- 转换前Oracle SQL：
SELECT LOWER('AaBbCcDd') Lowercase FROM DUAL;
LOWERCASE|
---------+
aabbccdd |
-- 转换后TDSQL-MySQL：
SELECT LOWER('AaBbCcDd') AS `Lowercase` FROM DUAL;
Lowercase|
---------+
aabbccdd |
5.2.2.4. REPLACE
- 语法
REPLACE(char1,char2[,char3])- 描述
- 该函数将字符串 char1 中与 char2 匹配的子字符串替换为 char3
参数解释
参数 |
说明 |
|---|---|
char1 |
指定等待替换的字符串。数据类型可以是 CHAR 、VARCHAR2、NCHAR、NVARCHAR2 和 CLOB |
char2 |
指定需要替换的字符串。数据类型可以是 CHAR 、VARCHAR2、NCHAR、NVARCHAR2 和 CLOB |
char3 |
指定替换字符串，默认为空，表示删除，不是空格.说明 如果 char3 缺省或者为 NULL，那么所有 char1 中出现的 char2 都将被移除。如果 char2 为空值，那么结果就是 char1 |
示例
-- 转换前Oracle SQL：
SELECT REPLACE('unisql','u','U') "replace" FROM DUAL;
replace|
-------+
Unisql |
-- 转换后TDSQL-MySQL：
SELECT REPLACE('unisql', 'u', 'U') AS `replace` FROM DUAL;
replace|
-------+
Unisql |
5.2.2.5. UPPER
- 语法
UPPER(char)- 描述
- 该函数将字符串中英文字母全部转为大写
参数解释
参数 |
说明 |
|---|---|
char |
要转换的字符串，数据类型可以为 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB. |
示例
-- 转换前Oracle SQL：
SELECT UPPER('unisql') "UPPER" FROM DUAL;
UPPER |
------+
UNISQL|
-- 转换后TDSQL-MySQL：
SELECT UPPER('unisql') AS `UPPER` FROM DUAL;
UPPER |
------+
UNISQL|
5.2.2.6. REVERSE
- 语法
REVERSE(str)- 描述
- 该函数将字符串进行倒序
参数解释
参数 |
说明 |
|---|---|
str |
要倒序的字符串 |
示例
-- 转换前Oracle SQL：
SELECT REVERSE('unIsql') FROM DUAL;
REVERSE('UNISQL')|
-----------------+
lqsInu |
-- 转换后TDSQL-MySQL：
SELECT REVERSE('unIsql') FROM DUAL;
REVERSE('unIsql')|
-----------------+
lqsInu |
5.2.2.7. ASCII
- 语法
ASCII(str)- 描述
- 该函数返回字符串 str 最左侧字符的 ASCII 码
参数解释
参数 |
说明 |
|---|---|
str |
字符串.如果 str 为空字符串，则该函数返回 0。如果 str 为 NULL，则该函数返回 NULL |
示例
-- 转换前Oracle SQL：
SELECT ASCII('a'),ASCII('ab'),ASCII('d'),ASCII('1'),ASCII(''),ASCII(NULL) FROM DUAL;
ASCII('A')|ASCII('AB')|ASCII('D')|ASCII('1')|ASCII('')|ASCII(NULL)|
----------+-----------+----------+----------+---------+-----------+
97| 97| 100| 49| | |
-- 转换后TDSQL-MySQL：
SELECT ASCII('a'),ASCII('ab'),ASCII('d'),ASCII('1'),ASCII(''),ASCII(NULL) FROM DUAL;
ASCII('a')|ASCII('ab')|ASCII('d')|ASCII('1')|ASCII('')|ASCII(NULL)|
----------+-----------+----------+----------+---------+-----------+
97| 97| 100| 49| 0| |
5.2.2.8. LENGTH
- 语法
LENGTH(str)- 描述
- 该函数返回 str 的字符长度
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
示例
-- 转换前Oracle SQL：
SELECT LENGTH('中国'), LENGTH('hello') FROM DUAL;
LENGTH('中国')|LENGTH('HELLO')|
------------+---------------+
2| 5|
-- 转换后TDSQL-MySQL：
SELECT char_length('中国'),char_length('hello') FROM DUAL
char_length('中国')|char_length('hello')|
-----------------+--------------------+
2| 5|
-- 在Oracle中，\\n会被视为两个字符，在Mysql中，\\n会被视为一个字符，下面语句执行后会返回6
-- 转换前Oracle SQL：
SELECT LENGTH('abc\n\t\r') FROM dual
LENGTH('ABC\N\T\R')|
-------------------+
9|
-- 转换后TDSQL-MySQL：
SELECT char_length('abc\n\t\r') FROM dual;
char_length('abc\n\t\r')|
------------------------+
6|
5.2.2.9. LENGTHB
- 语法
LENGTHB(str)- 描述
- 该函数返回 str 的字节长度，与字符集有关
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
示例
-- 转换前Oracle SQL：
SELECT LENGTHB('中国'), LENGTHB('hello') FROM DUAL;
LENGTHB('中国')|LENGTHB('HELLO')|
-------------+---------------+
6| 5|
-- 转换后TDSQL-MySQL：
SELECT length('中国'),char_length('hello') FROM DUAL;
length('中国')|char_length('hello')|
------------+--------------------+
6| 5|
-- 在Oracle中，\\n会被视为两个字节长度，在Mysql中，\\n会被视为一个字节长度，下面语句执行后会返回6
-- 转换前Oracle SQL：
SELECT LENGTHB('abc\n\t\r') FROM dual;
LENGTHB('ABC\N\T\R')|
--------------------+
9|
-- 转换后TDSQL-MySQL：
SELECT length('abc\n\t\r') FROM dual;
length('abc\n\t\r')|
-------------------+
6|
5.2.2.10. TRIM
- 语法
TRIM([[{BOTH | LEADING | TRAILING}] FROM] str- 描述
- 删除字符串所有前缀和/或后缀，默认为 BOTH。参数中包含 NULL 时，返回 NULL
参数解释
参数 |
说明 |
|---|---|
BOTH |
删除字符串所有前缀和后缀 |
LEADING |
删除字符串前缀 |
TRAILING |
删除字符串后缀 |
str |
要操作的字符串 |
示例
-- 转换前Oracle SQL：
SELECT TRIM(' bar '),TRIM(LEADING 'x' FROM 'xxxbarxxx'), TRIM(BOTH 'x' FROM 'xxxbarxxx'),TRIM(TRAILING 'x' FROM 'xxxbarxxx'),TRIM(BOTH 'x' FROM NULL),TRIM(NULL) FROM DUAL;
TRIM('BAR')|TRIM(LEADING'X'FROM'XXXBARXXX')|TRIM(BOTH'X'FROM'XXXBARXXX')|TRIM(TRAILING'X'FROM'XXXBARXXX')|TRIM(BOTH'X'FROMNULL)|TRIM(NULL)|
-----------+-------------------------------+----------------------------+--------------------------------+---------------------+----------+
bar |barxxx |bar |xxxbar | | |
-- 转换后TDSQL-MySQL：
SELECT TRIM(' bar '),TRIM(LEADING 'x' FROM 'xxxbarxxx'),TRIM(BOTH 'x' FROM 'xxxbarxxx'),TRIM(TRAILING 'x' FROM 'xxxbarxxx'),TRIM(BOTH 'x' FROM NULL),TRIM(NULL) FROM DUAL;
TRIM(' bar ')|TRIM(LEADING 'x' FROM 'xxxbarxxx')|TRIM(BOTH 'x' FROM 'xxxbarxxx')|TRIM(TRAILING 'x' FROM 'xxxbarxxx')|TRIM(BOTH 'x' FROM NULL)|TRIM(NULL)|
-------------+----------------------------------+-------------------------------+-----------------------------------+------------------------+----------+
bar |barxxx |bar |xxxbar | | |
5.2.2.11. LPAD
- 语法
LPAD(str,len,padstr)- 描述
- 使用字符串 padstr 从左侧填充字符串 str，直到长度为 len 时为止
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
len |
要填充长度 |
padstr |
要填充的字符串 |
示例
-- 转换前Oracle SQL：
SELECT
LPAD('1223', 3),
LPAD('1223', 4),
LPAD('1223', 5),
LPAD('1223', 6, '2+1'),
LPAD('1223', 7, ''),
LPAD('1223', 0),
LPAD('1223',-1)
FROM dual;
LPAD('1223',3)|LPAD('1223',4)|LPAD('1223',5)|LPAD('1223',6,'2+1')|LPAD('1223',7,'')|LPAD('1223',0)|LPAD('1223',-1)|
--------------+--------------+--------------+--------------------+-----------------+--------------+---------------+
122 |1223 | 1223 |2+1223 | | | |
-- 转换后TDSQL-MySQL：
SELECT
if(length('1223')>=3, LPAD('1223', 3, ''), '1223'),
if(length('1223')>=4, LPAD('1223', 4, ''), '1223'),
if(length('1223')>=5, LPAD('1223', 5, ''), '1223'),
if('2+1' IS NULL OR length('2+1')=0, lpad('1223', 6, NULL), LPAD('1223', 6, '2+1')),
if('' IS NULL OR length('')=0, lpad('1223', 7, NULL), LPAD('1223', 7, '')),
if(length('1223')>=0, LPAD('1223', 0, ''), '1223'),
if(length('1223')>=-1, LPAD('1223', -1, ''), '1223')
FROM dual;
if(length('1223')>=3, LPAD('1223', 3, ''), '1223')|if(length('1223')>=4, LPAD('1223', 4, ''), '1223')|if(length('1223')>=5, LPAD('1223', 5, ''), '1223')|if('2+1' IS NULL OR length('2+1')=0, lpad('1223', 6, NULL), LPAD('1223', 6, '2+1'))|if('' IS NULL OR length('')=0, lpad('1223', 7, NULL), LPAD('1223', 7, ''))|if(length('1223')>=0, LPAD('1223', 0, ''), '1223')|if(length('1223')>=-1, LPAD('1223', -1, ''), '1223')|
--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+-----------------------------------------------------------------------------------+--------------------------------------------------------------------------+--------------------------------------------------+----------------------------------------------------+
122 |1223 |1223 |2+1223 | | | |
5.2.2.12. RPAD
- 语法
RPAD(str,len,padstr)- 描述
- 使用字符串 padstr 从右侧填充字符串 str，直到长度为 len 时为止
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
len |
要填充长度 |
padstr |
要填充的字符串 |
示例
-- 转换前Oracle SQL：
SELECT
RPAD('1223', 3),
RPAD('1223', 4),
RPAD('1223', 5),
RPAD('1223', 6, '2+1'),
RPAD('1223', 7, ''),
RPAD('1223', 0),
RPAD('1223',-1)
FROM
dual;
RPAD('1223',3)|RPAD('1223',4)|RPAD('1223',5)|RPAD('1223',6,'2+1')|RPAD('1223',7,'')|RPAD('1223',0)|RPAD('1223',-1)|
--------------+--------------+--------------+--------------------+-----------------+--------------+---------------+
122 |1223 |1223 |12232+ | | | |
-- 转换后TDSQL-MySQL：
SELECT
if(length('1223')>=3, RPAD('1223', 3, ''), '1223'),
if(length('1223')>=4, RPAD('1223', 4, ''), '1223'),
if(length('1223')>=5, RPAD('1223', 5, ''), '1223'),
if('2+1' IS NULL OR length('2+1')=0, rpad('1223', 6, NULL), RPAD('1223', 6, '2+1')),
if('' IS NULL OR length('')=0, rpad('1223', 7, NULL), RPAD('1223', 7, '')),
if(length('1223')>=0, RPAD('1223', 0, ''), '1223'),if(length('1223')>=-1, RPAD('1223', -1, ''), '1223')
FROM dual;
if(length('1223')>=3, RPAD('1223', 3, ''), '1223')|if(length('1223')>=4, RPAD('1223', 4, ''), '1223')|if(length('1223')>=5, RPAD('1223', 5, ''), '1223')|if('2+1' IS NULL OR length('2+1')=0, rpad('1223', 6, NULL), RPAD('1223', 6, '2+1'))|if('' IS NULL OR length('')=0, rpad('1223', 7, NULL), RPAD('1223', 7, ''))|if(length('1223')>=0, RPAD('1223', 0, ''), '1223')|if(length('1223')>=-1, RPAD('1223', -1, ''), '1223')|
--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+-----------------------------------------------------------------------------------+--------------------------------------------------------------------------+--------------------------------------------------+----------------------------------------------------+
122 |1223 |1223 |12232+ | | | |
5.2.2.13. SUBSTR
语法
SUBSTR(str, pos)
SUBSTR(str, pos, len)
- 描述
- 返回 str 的子字符串，起始位置为 pos，长度为 len。参数中包含 NULL 时，返回 NULL
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
pos |
子字符串的起始位置 |
len |
子字符串的长度 |
示例
-- 转换前Oracle SQL：
SELECT SUBSTR('abcdefg', 3),SUBSTR('abcdefg', 3, 2),SUBSTR('abcdefg', -3),SUBSTR('abcdefg', 3, -2) FROM DUAL;
SUBSTR('ABCDEFG',3)|SUBSTR('ABCDEFG',3,2)|SUBSTR('ABCDEFG',-3)|SUBSTR('ABCDEFG',3,-2)|
-------------------+---------------------+--------------------+----------------------+
cdefg |cd |efg | |
-- 转换后TDSQL-MySQL：
SELECT substr('abcdefg', if(3=0, 1, 3), length('abcdefg')),substr('abcdefg', if(3=0, 1, 3), 2),substr('abcdefg', if(-3=0, 1, -3), length('abcdefg')),substr('abcdefg', if(3=0, 1, 3), -2) FROM DUAL;
substr('abcdefg', if(3=0, 1, 3), length('abcdefg'))|substr('abcdefg', if(3=0, 1, 3), 2)|substr('abcdefg', if(-3=0, 1, -3), length('abcdefg'))|substr('abcdefg', if(3=0, 1, 3), -2)|
---------------------------------------------------+-----------------------------------+-----------------------------------------------------+------------------------------------+
cdefg |cd |efg | |
5.2.2.14. INSTR
- 语法
INSTR(str,substr)- 描述
- 返回字符串 str 中子字符串的第一个出现位置
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
substr |
子字符串 |
示例
-- 转换前Oracle SQL：
SELECT INSTR('Unisql','U') i1,INSTR('Unisql','u') i2,INSTR('Unisql','n') i3,INSTR('Unisql','a') i4,INSTR('Unisql','') i5,INSTR('Unisql',NULL) i6,INSTR('foobarbar', 'bar') i7, INSTR('xbar', 'foobar') i8,INSTR('北京欢迎你','北') i9 FROM DUAL;
I1|I2|I3|I4|I5|I6|I7|I8|I9|
--+--+--+--+--+--+--+--+--+
1| 0| 2| 0| | | 4| 0| 1|
-- 转换后TDSQL-MySQL：
SELECT instr('Unisql', if(BINARY 'U'='', NULL, BINARY 'U')) AS `i1`,instr('Unisql', if(BINARY 'u'='', NULL, BINARY 'u')) AS `i2`,instr('Unisql', if(BINARY 'n'='', NULL, BINARY 'n')) AS `i3`,instr('Unisql', if(BINARY 'a'='', NULL, BINARY 'a')) AS `i4`,instr('Unisql', if(BINARY ''='', NULL, BINARY '')) AS `i5`,instr('Unisql', if(BINARY NULL='', NULL, BINARY NULL)) AS `i6`,instr('foobarbar', if(BINARY 'bar'='', NULL, BINARY 'bar')) AS `i7`,instr('xbar', if(BINARY 'foobar'='', NULL, BINARY 'foobar')) AS `i8`,instr('北京欢迎你', if(BINARY '北'='', NULL, BINARY '北')) AS `i9` FROM DUAL;
i1|i2|i3|i4|i5|i6|i7|i8|i9|
--+--+--+--+--+--+--+--+--+
1| 0| 2| 0| | | 4| 0| 1|
5.2.2.15. LTRIM
- 语法
LTRIM(char,[set])- 描述
- 从 char 的左端删除集合 set 中包含的所有字符
参数解释
参数 |
说明 |
|---|---|
char |
要操作的字符串 |
set |
要删除的字符集合 |
示例
-- 转换前Oracle SQL：
SELECT LTRIM(' 2023-09-21 ', ' 0123'),LTRIM(' 2023-09-21') FROM DUAL;
LTRIM('2023-09-21','0123')|LTRIM('2023-09-21')|
--------------------------+-------------------+
-09-21 |2023-09-21 |
-- 转换后TDSQL-MySQL：
SELECT `unisql`.unisql_ltrim(' 2023-09-21 ', ' 0123'),`unisql`.unisql_ltrim(' 2023-09-21', ' ') FROM DUAL
`unisql`.unisql_ltrim(' 2023-09-21 ', ' 0123')|`unisql`.unisql_ltrim(' 2023-09-21', ' ')|
----------------------------------------------+------------------------------------------+
-09-21 |2023-09-21 |
5.2.2.16. RTRIM
- 语法
RTRIM(char,[set])- 描述
- 从 char 的右端删除集合 set 中包含的所有字符
参数解释
参数 |
说明 |
|---|---|
char |
要操作的字符串 |
set |
要删除的字符集合 |
示例
-- 转换前Oracle SQL：
SELECT RTRIM(' 2023-09-21 ', ' 0123'),RTRIM(' 2023-09-21') FROM DUAL;
RTRIM('2023-09-21','0123')|RTRIM('2023-09-21')|
--------------------------+-------------------+
2023-09- | 2023-09-21 |
-- 转换后TDSQL-MySQL：
SELECT `unisql`.unisql_rtrim(' 2023-09-21 ', ' 0123'),`unisql`.unisql_rtrim(' 2023-09-21', ' ') FROM DUAL
`unisql`.unisql_rtrim(' 2023-09-21 ', ' 0123')|`unisql`.unisql_rtrim(' 2023-09-21', ' ')|
----------------------------------------------+------------------------------------------+
2023-09- | 2023-09-21 |
5.2.2.17. REGEXP_SUBSTR
语法
REGEXP_SUBSTR(source_char, pattern
[, position [, occurrence ] ])
- 描述
- 该函数允许使用正则表达式搜索字符串并返回匹配字符串
参数解释
参数 |
说明 |
|---|---|
source_char |
指定用作搜索值的字符表达式 |
pattern |
指定正则表达式截取规则。它通常是一个文本字面量 |
position |
指定开始正则表达式匹配的起始位置，取值是一个正整数，为可选项。默认值是 1，表示从第一个字符开始搜索 |
occurrence |
指定 source_char 中第几个正则表达式匹配位置，为可选项。默认值为 1 |
示例
-- 转换前Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+') from dual;
REGEXP_SUBSTR('A11A22A33A44','A[[:DIGIT:]]+')|
---------------------------------------------+
a11 |
-- 转换后TDSQL-MySQL：
SELECT `unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 1) FROM dual;
`unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 1)|
----------------------------------------------------------------------+
a11 |
-- 转换前Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+', 1) from dual;
REGEXP_SUBSTR('A11A22A33A44','A[[:DIGIT:]]+',1)|
-----------------------------------------------+
a11 |
-- 转换后TDSQL-MySQL：
SELECT `unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 1) FROM dual;
`unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 1)|
----------------------------------------------------------------------+
a11 |
-- 转换前Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+', 1, 2) FROM DUAL;
REGEXP_SUBSTR('A11A22A33A44','A[[:DIGIT:]]+',1,2)|
-------------------------------------------------+
a22 |
-- 转换后TDSQL-MySQL：
SELECT `unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 2) FROM DUAL;
`unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 2)|
----------------------------------------------------------------------+
a22 |
5.2.3. 时间日期函数
5.2.3.1. TRUNC(date)
- 语法
TRUNC(date,[fmt])- 描述
- 该函数返回以参数 fmt 为单位距离的离指定日期 date 最近的日期时间值，并且返回的日期值在 date 之前
参数解释
参数 |
说明 |
|---|---|
date |
DATE 数据类型 |
fmt |
指定了函数返回值与 date 的距离单位 |
fmt的取值如下：
fmt 参数的取值 |
说明 |
|---|---|
J |
默认值，最近 0 点日期。 |
DAY、DY、D、DDD、DD |
返回离指定日期最近的星期日。 |
MONTH、MON、MM、RM |
返回离指定日期最近的月的第一天日期。 |
Q |
返回离指定日期最近的季的日期。 |
YYYY、YYY、YY、Y |
多个 y 表示不同的精度，返回离指定日期最近的年的第一个日期。 |
CC、SCC |
返回离指定日期最近的世纪的初日期。 |
示例
-- 转换前Oracle SQL：
SELECT TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'YEAR'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'MONTH'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'DDD')FROM DUAL;
TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'YEAR')|TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'MONTH')|TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'DDD')|
------------------------------------------------------------------+-------------------------------------------------------------------+-----------------------------------------------------------------+
2022-01-01 00:00:00.000| 2022-04-01 00:00:00.000| 2022-04-19 00:00:00.000|
-- 转换后TDSQL-MySQL：
SELECT `unisql`.unisql_trunc(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'YEAR'),`unisql`.unisql_trunc(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'MONTH'),`unisql`.unisql_trunc(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'DDD') FROM DUAL;
`unisql`.unisql_trunc(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'YEAR')|`unisql`.unisql_trunc(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'MONTH')|`unisql`.unisql_trunc(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'DDD')|
--------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------+
2022-01-01 00:00:00.000| 2022-04-01 00:00:00.000| 2022-04-19 00:00:00.000|
5.2.3.2. LAST_DAY
- 语法
LAST_DAY(date)- 描述
- 返回 date 当月最后一天的日期值
参数解释
参数 |
说明 |
|---|---|
date |
日期类型参数 |
示例
-- 转换前Oracle SQL：
SELECT LAST_DAY(SYSDATE),LAST_DAY(TO_DATE('2023/09/21','YYYY/MM/DD')) FROM DUAL;
LAST_DAY(SYSDATE) |LAST_DAY(TO_DATE('2023/09/21','YYYY/MM/DD'))|
-----------------------+--------------------------------------------+
2023-12-31 00:59:15.000| 2023-09-30 00:00:00.000|
-- 转换后TDSQL-MySQL：
SELECT date_add(last_day(current_timestamp()), INTERVAL date_format(current_timestamp(), '%H:%i:%s') HOUR_SECOND),date_add(last_day(CAST(str_to_date('2023/09/21', '%Y/%m/%d') AS DATETIME)), INTERVAL date_format(CAST(str_to_date('2023/09/21', '%Y/%m/%d') AS DATETIME), '%H:%i:%s') HOUR_SECOND) FROM DUAL;
date_add(last_day(current_timestamp()), INTERVAL date_format(current_timestamp(), '%H:%i:%s') HOUR_SECOND)|date_add(last_day(CAST(str_to_date('2023/09/21', '%Y/%m/%d') AS DATETIME)), INTERVAL date_format(CAST(str_to_date('2023/09/21', '%Y/%m/%d') AS DATETIME), '%H:%i:%s') HOUR_SECOND)|
----------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2023-12-31 00:59:15.000| 2023-09-30 00:00:00.000|
5.2.3.3. EXTRACT
- 语法
EXTRACT(unit FROM date)- 描述
- 以整数类型返回 date 的指定部分值
参数解释
参数 |
说明 |
|---|---|
date |
日期类型参数 |
示例
-- 转换前Oracle SQL：
SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-09-06 12:38:40') "HOUR",
EXTRACT(MINUTE FROM TIMESTAMP '2023-09-06 12:38:40') "MINUTE",
EXTRACT(SECOND FROM TIMESTAMP '2023-09-06 12:38:40') "SECOND",
EXTRACT(DAY FROM TIMESTAMP '2023-09-06 12:38:40') "DAY",
EXTRACT(MONTH FROM TIMESTAMP '2023-09-06 12:38:40') "MONTH",
EXTRACT(YEAR FROM TIMESTAMP '2023-09-06 12:38:40') "YEAR"
FROM DUAL;
HOUR|MINUTE|SECOND|DAY|MONTH|YEAR|
----+------+------+---+-----+----+
12| 38| 40| 6| 9|2023|
-- 转换后TDSQL-MySQL：
SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-09-06 12:38:40') AS `HOUR`,EXTRACT(MINUTE FROM TIMESTAMP '2023-09-06 12:38:40') AS `MINUTE`,EXTRACT(SECOND FROM TIMESTAMP '2023-09-06 12:38:40') AS `SECOND`,EXTRACT(DAY FROM TIMESTAMP '2023-09-06 12:38:40') AS `DAY`,EXTRACT(MONTH FROM TIMESTAMP '2023-09-06 12:38:40') AS `MONTH`,EXTRACT(YEAR FROM TIMESTAMP '2023-09-06 12:38:40') AS `YEAR` FROM DUAL;
HOUR|MINUTE|SECOND|DAY|MONTH|YEAR|
----+------+------+---+-----+----+
12| 38| 40| 6| 9|2023|
5.2.3.4. SYSDATE
- 语法
SYSDATE- 描述
- 返回当前日期和时间，MySQL相关数据库可指定时区设置，影响此函数的返回值
- 参数解释
- 无
示例
-- 转换前Oracle SQL：
SELECT SYSDATE FROM DUAL;
SYSDATE |
-----------------------+
2023-12-20 09:51:46.000|
-- 转换后TDSQL-MySQL：
SELECT CURRENT_TIMESTAMP() FROM DUAL;
CURRENT_TIMESTAMP() |
-----------------------+
2024-02-02 14:31:05.000|
5.2.3.5. TO_CHAR (datetime)
- 语法
TO_CHAR({ datetime} [, fmt])- 描述
- 该函数将日期时间按照参数 fmt 指定的格式转换为 VARCHAR2 数据类型的值
参数解释
参数 |
说明 |
|---|---|
datetime |
datetime 属于日期时间， DATE、TIMESTAMP数据类型 |
fmt |
指定输出格式 |
示例
-- 转换前Oracle SQL：
SELECT
TO_CHAR(TO_DATE('2023-04-20', 'YYYY-MM-DD'),'YYYY-MM-DD') AS a1
FROM DUAL;
A1 |
----------+
2023-04-20|
-- 转换后TDSQL-MySQL：
SELECT date_format(CAST(str_to_date('2023-04-20', '%Y-%m-%d') AS DATETIME), '%Y-%m-%d') AS `a1` FROM DUAL
a1 |
----------+
2023-04-20|
5.2.3.6. TO_TIMESTAMP
- 语法
TO_TIMESTAMP (char，[fmt])- 描述
- 该函数将字符串转换为 TIMESTAMP 数据类型
参数解释
参数 |
说明 |
|---|---|
datetime |
datetime 属于日期时间， DATE、TIMESTAMP数据类型 |
fmt |
指定输出格式 |
示例
-- 转换前Oracle SQL：
SELECT
to_timestamp('2020-02-02', 'yyyy-mm-dd') AS a1,
to_timestamp('2020-02-02 02', 'yyyy-mm-dd hh24') AS a2
FROM
dual
A1 |A2 |
-----------------------+-----------------------+
2020-02-02 00:00:00.000|2020-02-02 02:00:00.000|
-- 转换后TDSQL-MySQL：
SELECT CAST(str_to_date('2020-02-02', '%Y-%m-%d') AS DATETIME(6)) AS `a1`,CAST(str_to_date('2020-02-02 02', '%Y-%m-%d %H') AS DATETIME(6)) AS `a2` FROM dual;
a1 |a2 |
-----------------------+-----------------------+
2020-02-02 00:00:00.000|2020-02-02 02:00:00.000|
5.2.3.7. CURRENT_DATE
- 语法
CURRENT_DATE- 描述
- 该函数返回当前会话时区的当前日期
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT CURRENT_DATE FROM DUAL;
-----------------------+
CURRENT_DATE |
-----------------------+
2023-12-20 19:11:29.000|
-- 转换后TDSQL-MySQL：
SELECT current_timestamp(0) FROM DUAL;
current_timestamp(0) |
-----------------------+
2023-12-20 19:11:29.000|
5.2.3.8. CURRENT_TIMESTAMP
- 语法
CURRENT_TIMESTAMP- 描述
- 该函数返回当前会话时区中的当前日期
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT CURRENT_TIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP |
-----------------------------+
2023-12-20 19:04:55.457 +0800|
-- 转换后TDSQL-MySQL：
SELECT CURRENT_TIMESTAMP() FROM DUAL;
CURRENT_TIMESTAMP() |
-----------------------+
2023-12-20 19:04:55.457|
5.2.3.9. ADD_MONTHS
- 语法
ADD_MONTHS(date, n)- 描述
- 该函数功能是返回日期 date 加上 n 个月后的日期值。
参数解释
参数 |
说明 |
|---|---|
date |
指定日期。该参数为 DATE 数据类型。 |
n |
整数或可以转换为一个整数的任意值。NUMBER 数据类型。 |
示例
-- 转换前Oracle SQL：
SELECT
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD') ,-3) AS a1,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 3)AS a2,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 1.1)AS a3,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 1.5)AS a4,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 1.8)AS a5,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 0)AS a6
FROM DUAL;
A1 |A2 |A3 |A4 |A5 |A6 |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-09-21 00:00:00.000|2024-03-21 00:00:00.000|2024-01-21 00:00:00.000|2024-01-21 00:00:00.000|2024-01-21 00:00:00.000|2023-12-21 00:00:00.000|
-- 转换后TDSQL-MySQL：
SELECT
date_add(CAST(str_to_date('2023-12-21', '%Y-%m-%d') AS DATETIME), INTERVAL floor(-3) MONTH) AS `a1`,
date_add(CAST(str_to_date('2023-12-21', '%Y-%m-%d') AS DATETIME), INTERVAL floor(3) MONTH) AS `a2`,
date_add(CAST(str_to_date('2023-12-21', '%Y-%m-%d') AS DATETIME), INTERVAL floor(1.1) MONTH) AS `a3`,
date_add(CAST(str_to_date('2023-12-21', '%Y-%m-%d') AS DATETIME), INTERVAL floor(1.5) MONTH) AS `a4`,
date_add(CAST(str_to_date('2023-12-21', '%Y-%m-%d') AS DATETIME), INTERVAL floor(1.8) MONTH) AS `a5`,
date_add(CAST(str_to_date('2023-12-21', '%Y-%m-%d') AS DATETIME), INTERVAL floor(0) MONTH) AS `a6`
FROM DUAL;
a1 |a2 |a3 |a4 |a5 |a6 |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-09-21 00:00:00.000|2024-03-21 00:00:00.000|2024-01-21 00:00:00.000|2024-01-21 00:00:00.000|2024-01-21 00:00:00.000|2023-12-21 00:00:00.000|
5.2.4. 通用比较函数
5.2.4.1. GREATEST
- 语法
GREATEST(value1, ...)- 描述
- 返回参数的最大值，和函数 LEAST() 相反
参数解释
参数 |
说明 |
|---|---|
value1, … |
参数至少为两个（如果只提供一个参数，则会报错）。如果参数中有 NULL，则该函数返回为 NULL |
示例
-- 转换前Oracle SQL：
SELECT GREATEST(2,1), GREATEST('2',1,0), GREATEST('a','b','c'), GREATEST('a', NULL, 'c'), GREATEST('2023-05-15','2023-09-06') from dual;
-------------+-----------------+---------------------+----------------------+-----------------------------------+
GREATEST(2,1)|GREATEST('2',1,0)|GREATEST('A','B','C')|GREATEST('A',NULL,'C')|GREATEST('2023-05-15','2023-09-06')|
-------------+-----------------+---------------------+----------------------+-----------------------------------+
2|2 |c | |2023-09-06 |
-- 转换后TDSQL-MySQL：
SELECT GREATEST(2, 1),GREATEST('2', 1, 0),GREATEST('a', 'b', 'c'),GREATEST('a', NULL, 'c'),GREATEST('2023-05-15', '2023-09-06') FROM dual
GREATEST(2, 1)|GREATEST('2', 1, 0)|GREATEST('a', 'b', 'c')|GREATEST('a', NULL, 'c')|GREATEST('2023-05-15', '2023-09-06')|
--------------+-------------------+-----------------------+------------------------+------------------------------------+
2|2 |c | |2023-09-06 |
5.2.4.2. LEAST
- 语法
LEAST(value1, ...)- 描述
- 返回参数的最小值，和 GREATEST() 函数相反
参数解释
参数 |
说明 |
|---|---|
value1, … |
参数至少为两个；如果参数中有 NULL，返回值为 NULL |
示例
-- 转换前Oracle SQL：
SELECT LEAST(2, null), LEAST('2',4,9), LEAST('a','b','c'), LEAST('a',NULL,'c'), LEAST('2014-05-15','2014-06-01') FROM dual;
-------------+--------------+------------------+-------------------+--------------------------------+
LEAST(2,NULL)|LEAST('2',4,9)|LEAST('A','B','C')|LEAST('A',NULL,'C')|LEAST('2014-05-15','2014-06-01')|
-------------+--------------+------------------+-------------------+--------------------------------+
|2 |a | |2014-05-15 |
-- 转换后TDSQL-MySQL：
SELECT LEAST(2, NULL),LEAST('2', 4, 9),LEAST('a', 'b', 'c'),LEAST('a', NULL, 'c'),LEAST('2014-05-15', '2014-06-01') FROM dual;
LEAST(2, NULL)|LEAST('2', 4, 9)|LEAST('a', 'b', 'c')|LEAST('a', NULL, 'c')|LEAST('2014-05-15', '2014-06-01')|
--------------+----------------+--------------------+---------------------+---------------------------------+
|2 |a | |2014-05-15 |
5.2.5. 转换函数
5.2.5.1. CAST
- 语法
CAST (expr AS type_name )- 描述
- 该函数用于将一种内置数据类型转换为另一种内置数据类型
参数解释
参数 |
说明 |
|---|---|
expr |
列名或者表达式。 |
AS |
用于分隔两个参数，在 AS 之前的是要处理的数据，在 AS 之后是要转换的数据类型。 |
type_name |
数据库的内建数据类型名称。 |
下表显示了哪些数据类型可以转换为其他内置数据类型
from BINARY_FLOAT / BINARY_DOUBLE |
from CHAR / VARCHAR2 |
from NUMBER |
from DATETIME/INTERVAL |
from RAW |
from NCHAR/ NVARCHAR2 |
|
|---|---|---|---|---|---|---|
to NUMBER |
yes |
yes |
yes |
no |
no |
yes |
警告
使用 CAST 函数转换数据类型时，需要注意以下内容：
对于以上列表中的转换，如果目标库对转换后的目标数据类型不支持，则会在目标库中执行时报错。
对于类似cast(‘6983140795139873811265107033372908879759234567’ AS number)这种超过38位精度的数字，转换后的SQL在目标库执行会存在报错或者精度错误的情况。
在TDSQL-MySQL中，CAST to NUMBER的时候存在四舍五入的情况，请使用CAST函数时注意目标类型的精度。
示例
-- 转换前Oracle SQL:
SELECT
CAST('100.2345' AS NUMBER) AS to_nu,
CAST('100.2345' AS NUMBER(*)) AS to_nu,
CAST('100.2345' AS NUMBER(*,0)) AS to_nu,
CAST('100.2345' AS NUMBER(*,2)) AS to_nu,
CAST('100.2345' AS NUMBER(4)) AS to_nu,
CAST('100.2345' AS NUMBER(4,0)) AS to_nu,
CAST('100.2345' AS NUMBER(9)) AS to_nu,
CAST('100.2345' AS NUMBER(9,0)) AS to_nu,
CAST('100.2345' AS NUMBER(18)) AS to_nu,
CAST('100.2345' AS NUMBER(18,0)) AS to_nu,
CAST('100.2345' AS NUMBER(38)) AS to_nu,
CAST('100.2345' AS NUMBER(38,0)) AS to_nu,
CAST('100.2345' AS NUMBER(10,2)) AS to_nu,
cast('100.2345' AS decimal) AS to_dec,
cast('100.2345' AS decimal(*)) AS to_dec,
cast('100.2345' AS decimal(*,0)) AS to_dec,
cast('100.2345' AS decimal(*,2)) AS to_dec,
cast('100.2345' AS decimal(4)) AS to_dec,
cast('100.2345' AS decimal(4,0)) AS to_dec,
cast('100.2345' AS decimal(9)) AS to_dec,
cast('100.2345' AS decimal(9,0)) AS to_dec,
CAST('100.2345' AS decimal(18)) AS to_dec,
CAST('100.2345' AS decimal(18,0)) AS to_dec,
CAST('100.2345' AS decimal(38)) AS to_dec,
CAST('100.2345' AS decimal(38,0)) AS to_dec,
CAST('100.2345' AS decimal(10,2)) AS to_dec,
cast('100.2345' AS dec) AS to_dec,
cast('100.2345' AS dec(*)) AS to_dec,
cast('100.2345' AS dec(*,0)) AS to_dec,
cast('100.2345' AS dec(*,2)) AS to_dec,
cast('100.2345' AS dec(4)) AS to_dec,
cast('100.2345' AS dec(4,0)) AS to_dec,
cast('100.2345' AS dec(9)) AS to_dec,
cast('100.2345' AS dec(9,0)) AS to_dec,
CAST('100.2345' AS dec(18)) AS to_dec,
CAST('100.2345' AS dec(18,0)) AS to_dec,
CAST('100.2345' AS dec(38)) AS to_dec,
CAST('100.2345' AS dec(38,0)) AS to_dec,
CAST('100.2345' AS dec(10,2)) AS to_dec,
cast('100.2345' AS numeric) AS to_numr,
cast('100.2345' AS numeric(*)) AS to_numr,
cast('100.2345' AS numeric(*,0)) AS to_numr,
cast('100.2345' AS numeric(*,2)) AS to_numr,
cast('100.2345' AS numeric(4)) AS to_numr,
cast('100.2345' AS numeric(4,0)) AS to_numr,
cast('100.2345' AS numeric(9)) AS to_numr,
cast('100.2345' AS numeric(9,0)) AS to_numr,
CAST('100.2345' AS numeric(18)) AS to_numr,
CAST('100.2345' AS numeric(18,0)) AS to_numr,
CAST('100.2345' AS numeric(38)) AS to_numr,
CAST('100.2345' AS numeric(38,0)) AS to_numr,
CAST('100.2345' AS numeric(10,2)) AS to_numr
FROM dual
TO_NU |TO_NU |TO_NU|TO_NU |TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU |TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|
--------+--------+-----+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
100.2345|100.2345| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100| 100.23| 100| 100| 100| 100| 100| 100| 100| 100| 100.23|
-- 转换后TDSQL-MySQL：
SELECT CAST('100.2345' AS DECIMAL) AS `to_nu`,CAST('100.2345' AS DECIMAL) AS `to_nu`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_nu`,CAST('100.2345' AS DECIMAL(38, 2)) AS `to_nu`,CAST('100.2345' AS DECIMAL(4)) AS `to_nu`,CAST('100.2345' AS DECIMAL(4)) AS `to_nu`,CAST('100.2345' AS DECIMAL(9)) AS `to_nu`,CAST('100.2345' AS DECIMAL(9)) AS `to_nu`,CAST('100.2345' AS DECIMAL(18)) AS `to_nu`,CAST('100.2345' AS DECIMAL(18)) AS `to_nu`,CAST('100.2345' AS DECIMAL(38)) AS `to_nu`,CAST('100.2345' AS DECIMAL(38)) AS `to_nu`,CAST('100.2345' AS DECIMAL(10, 2)) AS `to_nu`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_dec`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_dec`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_dec`,CAST('100.2345' AS DECIMAL(38, 2)) AS `to_dec`,CAST('100.2345' AS DECIMAL(4)) AS `to_dec`,CAST('100.2345' AS DECIMAL(4)) AS `to_dec`,CAST('100.2345' AS DECIMAL(9)) AS `to_dec`,CAST('100.2345' AS DECIMAL(9)) AS `to_dec`,CAST('100.2345' AS DECIMAL(18)) AS `to_dec`,CAST('100.2345' AS DECIMAL(18)) AS `to_dec`,CAST('100.2345' AS DECIMAL(38)) AS `to_dec`,CAST('100.2345' AS DECIMAL(38)) AS `to_dec`,CAST('100.2345' AS DECIMAL(10, 2)) AS `to_dec`,CAST('100.2345' AS DEC(38, 0)) AS `to_dec`,CAST('100.2345' AS DEC(38, 0)) AS `to_dec`,CAST('100.2345' AS DEC(38, 0)) AS `to_dec`,CAST('100.2345' AS DEC(38, 2)) AS `to_dec`,CAST('100.2345' AS DEC(4)) AS `to_dec`,CAST('100.2345' AS DEC(4)) AS `to_dec`,CAST('100.2345' AS DEC(9)) AS `to_dec`,CAST('100.2345' AS DEC(9)) AS `to_dec`,CAST('100.2345' AS DEC(18)) AS `to_dec`,CAST('100.2345' AS DEC(18)) AS `to_dec`,CAST('100.2345' AS DEC(38)) AS `to_dec`,CAST('100.2345' AS DEC(38)) AS `to_dec`,CAST('100.2345' AS DEC(10, 2)) AS `to_dec`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_numr`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_numr`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_numr`,CAST('100.2345' AS DECIMAL(38, 2)) AS `to_numr`,CAST('100.2345' AS DECIMAL(4)) AS `to_numr`,CAST('100.2345' AS DECIMAL(4)) AS `to_numr`,CAST('100.2345' AS DECIMAL(9)) AS `to_numr`,CAST('100.2345' AS DECIMAL(9)) AS `to_numr`,CAST('100.2345' AS DECIMAL(18)) AS `to_numr`,CAST('100.2345' AS DECIMAL(18)) AS `to_numr`,CAST('100.2345' AS DECIMAL(38)) AS `to_numr`,CAST('100.2345' AS DECIMAL(38)) AS `to_numr`,CAST('100.2345' AS DECIMAL(10, 2)) AS `to_numr` FROM dual
to_nu|to_nu|to_nu|to_nu |to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu |to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|
-----+-----+-----+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100| 100.23| 100| 100| 100| 100| 100| 100| 100| 100| 100.23|
5.2.5.2. TO_DATE
- 语法
TO_DATE(char [, fmt])- 描述
- 该函数将 CHAR、VARCHAR、NCHAR 或 NVARCHAR2 数据类型的字符转换为日期数据类型的值
参数解释
参数 |
说明 |
|---|---|
char |
CHAR、VARCHAR、NCHAR 或 NVARCHAR2 数据类型的值 |
fmt |
指定 char 的时间格式 |
示例
-- 转换前Oracle SQL：
SELECT
to_date('2020-02-02', 'yyyy-mm-dd') as a1,
to_date('2020-02-02 0201', 'yyyy-mm-dd hh24mi')as a2
FROM DUAL
A1 |A2 |
-----------------------+-----------------------+
2020-02-02 00:00:00.000|2020-02-02 02:01:00.000|
-- 转换后TDSQL-MySQL：
SELECT CAST(str_to_date('2020-02-02', '%Y-%m-%d') AS DATETIME) AS `a1`,CAST(str_to_date('2020-02-02 0201', '%Y-%m-%d %H%i') AS DATETIME) AS `a2` FROM DUAL;
a1 |a2 |
-----------------------+-----------------------+
2020-02-02 00:00:00.000|2020-02-02 02:01:00.000|
5.2.5.3. TO_NUMBER
- 语法
TO_NUMBER(expr)- 描述
- 该函数功能是将 CHAR、VARCHAR2等类型的字符串转换为 NUMBER 数值数据类型的值
参数解释
参数 |
说明 |
|---|---|
expr |
属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、BINARY_FLOAT 或 BINARY_DOUBLE 数据类型的数值。 |
示例
-- 转换前Oracle SQL：
SELECT TO_NUMBER('0123456'),TO_NUMBER('1.2'),TO_NUMBER('0'),TO_NUMBER(1.2),TO_NUMBER('-1'),TO_NUMBER(-1) FROM DUAL;
TO_NUMBER('0123456')|TO_NUMBER('1.2')|TO_NUMBER('0')|TO_NUMBER(1.2)|TO_NUMBER('-1')|TO_NUMBER(-1)|
--------------------+----------------+--------------+--------------+---------------+-------------+
123456| 1.2| 0| 1.2| -1| -1|
-- 转换后TDSQL-MySQL：
SELECT CAST('0123456' AS DECIMAL(65, 30)),CAST('1.2' AS DECIMAL(65, 30)),CAST('0' AS DECIMAL(65, 30)),CAST(1.2 AS DECIMAL(65, 30)),CAST('-1' AS DECIMAL(65, 30)),CAST(-1 AS DECIMAL(65, 30)) FROM DUAL;
CAST('0123456' AS DECIMAL(65, 30)) |CAST('1.2' AS DECIMAL(65, 30)) |CAST('0' AS DECIMAL(65, 30)) |CAST(1.2 AS DECIMAL(65, 30)) |CAST('-1' AS DECIMAL(65, 30)) |CAST(-1 AS DECIMAL(65, 30)) |
-------------------------------------+--------------------------------+--------------------------------+--------------------------------+---------------------------------+---------------------------------+
123456.000000000000000000000000000000|1.200000000000000000000000000000|0.000000000000000000000000000000|1.200000000000000000000000000000|-1.000000000000000000000000000000|-1.000000000000000000000000000000|
5.2.5.4. TO_CLOB
- 语法
TO_CLOB(lob_column | char)- 描述
- 该函数将 LOB 列或其他字符串中的 NCLOB 值转换为 CLOB 值。
参数解释
参数 |
说明 |
|---|---|
lob_column |
属于 LOB 列或其他字符串中的 NCLOB 值。 |
char |
属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB 或 NCLOB 类型的值。 |
示例
-- 转换前Oracle SQL：
SELECT TO_CLOB('1'),TO_CLOB(0),TO_CLOB(NULL), TO_CLOB('') FROM DUAL;
TO_CLOB('1')|TO_CLOB(0)|TO_CLOB(NULL)|TO_CLOB('')|
------------+----------+-------------+-----------+
1 |0 | | |
-- 转换后TDSQL-MySQL：
SELECT CAST('1' AS CHAR),CAST(0 AS CHAR),CAST(NULL AS CHAR),CAST('' AS CHAR) FROM DUAL
CAST('1' AS CHAR)|CAST(0 AS CHAR)|CAST(NULL AS CHAR)|CAST('' AS CHAR)|
-----------------+---------------+------------------+----------------+
1 |0 | | |
5.2.6. 编码解码函数
5.2.6.1. DECODE
- 语法
DECODE (condition, search, result [, search, result ...][, default])- 描述
- 该函数功能是将 condition 与每个 search 依次做比较，并返回对比结果
参数解释
参数 |
说明 |
|---|---|
condition |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式 |
search |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式 |
result |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式 |
default |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式 |
示例
-- 转换前Oracle SQL：
SELECT DECODE(SIGN((5*3-2)-(3*4-1)),0,'相等',1,'(5*3-2)大','(3*4-1)大'),
DECODE(INSTR('CLARK','S'), 0, '不含有 S', '含有 S') "CLARK",
DECODE(INSTR('KING','S'), 0, '不含有 S', '含有 S') "KING",
DECODE(INSTR('MILLER','S'), 0, '不含有 S', '含有 S') "MILLER",
DECODE(INSTR('ADAMS','S'), 0, '不含有 S', '含有 S') "ADAMS",
DECODE(INSTR('FORD','S'), 0, '不含有 S', '含有 S') "FORD",
DECODE(INSTR('JONES','S'), 0, '不含有 S', '含有 S') "JONES"
FROM DUAL;
DECODE(SIGN((5*3-2)-(3*4-1)),0,'相等',1,'(5*3-2)大','(3*4-1)大')|CLARK|KING |MILLER|ADAMS|FORD |JONES|
------------------------------------------------------------+-----+-----+------+-----+-----+-----+
(5*3-2)大 |不含有 S|不含有 S|不含有 S |含有 S |不含有 S|含有 S |
-- 转换后TDSQL-MySQL：
SELECT CASE WHEN SIGN((5*3-2)-(3*4-1))=0 THEN '相等' WHEN SIGN((5*3-2)-(3*4-1))=1 THEN '(5*3-2)大' ELSE '(3*4-1)大' END,CASE WHEN strpos('CLARK', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "CLARK",CASE WHEN strpos('KING', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "KING",CASE WHEN strpos('MILLER', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "MILLER",CASE WHEN strpos('ADAMS', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "ADAMS",CASE WHEN strpos('FORD', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "FORD",CASE WHEN strpos('JONES', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "JONES"
case |CLARK|KING |MILLER|ADAMS|FORD |JONES|
--------+-----+-----+------+-----+-----+-----+
(5*3-2)大|不含有 S|不含有 S|不含有 S |含有 S |不含有 S|含有 S |
5.2.7. 空值函数
5.2.7.1. NVL
- 语法
NVL(expr1, expr2)- 描述
- 该函数从两个表达式返回一个非空值。如果 expr1 与 expr2 的结果都为空值，则 NVL 函数返回 NULL
参数解释
参数 |
说明 |
|---|---|
expr1 |
指定第一个参数，数据类型可以是数据库内建数据类型中的任何数据类型。 |
expr2 |
指定第二个参数，数据类型可以是数据库内建数据类型中的任何数据类型。 |
示例
-- 转换前Oracle SQL：
SELECT NVL(10,'1'),NVL(NULL,1),NVL(0/1,1) FROM DUAL;
NVL(10,'1')|NVL(NULL,1)|NVL(0/1,1)|
-----------+-----------+----------+
10| 1| 0|
-- 转换后TDSQL-MySQL：
SELECT ifnull(10, '1'),ifnull(NULL, 1),ifnull(0/1, 1) FROM DUAL;
ifnull(10, '1')|ifnull(NULL, 1)|ifnull(0/1, 1)|
---------------+---------------+--------------+
10 | 1| 0.0000|
5.2.7.2. NULLIF
- 语法
NULLIF(expr1, expr2)- 描述
- 该函数用于比较两个参数表达式是否相等。如果 expr1 与 expr2 相等，则返回 NULL。如果 expr1 与 expr2 不相等，则返回 expr1。
参数解释
参数 |
说明 |
|---|---|
expr1 |
指定第一个参数，可以是任意数据类型的表达式。 |
expr2 |
指定第二个参数，如果 expr1 不是数值数据类型，则 expr2 的数据类型必须与 expr1 相同，否则数据库将返回错误。 |
示例
-- 转换前Oracle SQL：
SELECT NULLIF(3+4,6+1),NULLIF(3+4,6+2),NULLIF('3+4','6+1') FROM DUAL;
NULLIF(3+4,6+1)|NULLIF(3+4,6+2)|NULLIF('3+4','6+1')|
---------------+---------------+-------------------+
| 7|3+4 |
-- 转换后TDSQL-MySQL：
SELECT NULLIF(3+4, 6+1),NULLIF(3+4, 6+2),NULLIF('3+4', '6+1') FROM DUAL
NULLIF(3+4,6+1)|NULLIF(3+4,6+2)|NULLIF('3+4','6+1')|
---------------+---------------+-------------------+
| 7|3+4 |
5.2.8. 环境和标识符函数
5.2.8.1. SYS_GUID
- 语法
SYS_GUID()- 描述
- 该函数生成并返回由 16 个字节组成的全局唯一标识符，即生成一个全局唯一序列号。这个全局唯一序列号通常是一个 RAW（原始） 数据类型。如果在客户端工具或应用程序中以文本形式显示，可能会看到乱码，因为 RAW 类型的数据在文本中可能不可读。可以使用 RAWTOHEX 函数将其转换为十六进制字符串。
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT SYS_GUID() FROM DUAL;
SYS_GUID() |
----------------+
íx OH xàc) çt|
-- 转换后TDSQL-MySQL：
SELECT uuid() FROM DUAL;
uuid() |
------------------------------------+
4744ece6-9f34-11ee-9bde-005056b27854|
5.2.8.2. USER
- 语法
USER- 描述
- 返回当前的用户名与主机名
- 参数解释
无
示例
-- 转换前Oracle SQL：
SELECT USER FROM DUAL;
USER |
------+
TEST|
-- 转换后TDSQL-MySQL：
SELECT user() FROM DUAL
user() |
--------------------------+
test@10.188.120.241|
5.2.9. 层次函数
暂不支持，建议业务调整
5.2.10. 聚合函数
5.2.10.1. AVG
- 语法
AVG([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]- 描述
- 该函数将数值类型或者可以转换成数值类型的表达式作为参数求平均值。可以将其用作聚合或分析函数。
说明
作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。
警告
统一SQL暂时不支持AVG作为分析函数的用法，不支持over子句。
参数解释
参数 |
说明 |
|---|---|
DISTINCT ALL |
计算时是否去重。为可选项，默认值 ALL。ALL： 计算所有值，包含重复行，且忽略值为 NULL 的行。DISTINCT： 去除重复行，且忽略值为 NULL 的行。 |
expr |
指定要计算的列名。列的数据类型是数值类型或者可以转换成数值类型的表达式 |
OVER |
暂时不支持 |
示例
-- 建表语句：
CREATE TABLE unisql_employee(employee_id int,name varchar(30),salary int,department_id int,hire_date varchar(10),commission_pct number);
INSERT INTO unisql_employee values(1,'JACK',5000,1,'2023-01-01',0.05);
INSERT INTO unisql_employee values(2,'TOM',10000,1,'2023-02-01',0.15);
INSERT INTO unisql_employee values(3,'LINDA',15000,1,'2023-03-01',0.20);
INSERT INTO unisql_employee values(4,'ADA',20000,2,'2023-04-01',0.10);
INSERT INTO unisql_employee values(5,'TINA',30000,2,'2023-05-01',0.20);
INSERT INTO unisql_employee values(6,'KATE',50000,3,'2023-06-01',0.30);
-- 转换前Oracle SQL：
SELECT AVG(salary) FROM unisql_employee;
AVG(SALARY) |
----------------------------------------+
21666.6666666666666666666666666666666667|
-- 转换后TDSQL-MySQL：
SELECT AVG(salary) FROM unisql_employee;
AVG(`salary`)|
-------------+
21666.6667|
5.2.10.2. CORR
- 语法
TDSQL-MySQL不支持
5.2.10.3. COUNT
- 语法
COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]- 描述
- 该函数用于返回查询 expr 的行数。可以将其用作聚合或分析函数。
说明
作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。
警告
统一SQL暂时不支持COUNT作为分析函数的用法，不支持over子句。
参数解释
参数 |
说明 |
|---|---|
DISTINCT ALL |
计算时是否去重。为可选项，默认值 ALL。ALL： 计算所有值，包含重复行，且忽略值为 NULL 的行。DISTINCT： 去除重复行，且忽略值为 NULL 的行。 |
expr |
指定参与计算的列名 |
OVER |
暂时不支持 |
星号(*) |
表示返回满足条件的所有行，且包含重复行和空行。 |
示例
-- 转换前Oracle SQL：
SELECT count(1),count(*),count(employee_id),count(DISTINCT(department_id)) FROM unisql_employee ke;
COUNT(1)|COUNT(*)|COUNT(EMPLOYEE_ID)|COUNT(DISTINCT(DEPARTMENT_ID))|
--------+--------+------------------+------------------------------+
6| 6| 6| 3|
-- 转换后TDSQL-MySQL：
SELECT count(1),count(1),count(`employee_id`),count(DISTINCT (`department_id`)) FROM `unisql_employee` AS `ke`;
count(1)|count(1)|count(`employee_id`)|count(DISTINCT (`department_id`))|
--------+--------+--------------------+---------------------------------+
6| 6| 6| 3|
5.2.10.4. COVAR_POP
- 语法
TDSQL-MySQL不支持
5.2.10.5. COVAR_SAMP
- 语法
TDSQL-MySQL不支持
5.2.10.6. CUME_DIST
- 语法
TDSQL-MySQL不支持
5.2.10.7. DENSE_RANK
- 语法
TDSQL-MySQL不支持
5.2.10.8. LISTAGG
- 语法
LISTAGG (measure_expr [,'delimiter']) [ WITHIN GROUP ] (order_by_clause)- 描述
- 该函数用于列转行，LISTAGG 对 ORDER BY 子句中指定的每个组内的数据进行排序，然后合并度量列的值
参数解释
参数 |
说明 |
|---|---|
measure_expr |
可以是任何表达式。度量列中的空值将被忽略。 |
delimiter |
指定用于分隔度量值的字符串。此子句是可选的，默认为 NULL。 |
示例
-- 转换前Oracle SQL：
SELECT LISTAGG(name, '; ') WITHIN GROUP (ORDER BY employee_id,name) AS rk FROM unisql_employee WHERE department_id=1;
RK |
----------------+
JACK; TOM; LINDA|
-- 转换后TDSQL-MySQL：
SELECT group_concat(`name` ORDER BY `employee_id`,`name` SEPARATOR '; ') AS `rk` FROM `unisql_employee` WHERE `department_id`=1;
rk |
----------------+
JACK; TOM; LINDA|
5.2.10.9. MAX
- 语法
MAX({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]- 描述
- 该函数返回参数中指定的列中的最大值。可以将其用作聚合或分析函数。
说明
作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。
警告
统一SQL暂时不支持MAX作为分析函数的用法，不支持over子句。
参数解释
参数 |
说明 |
|---|---|
DISTINCT ALL |
计算时是否去重。为可选项，默认值 ALL。ALL： 计算所有值，包含重复行，且忽略值为 NULL 的行。DISTINCT： 去除重复行，且忽略值为 NULL 的行。 |
expr |
可为数值、字符、日期型或其它类型的数据列或表达式 |
OVER |
暂时不支持 |
示例
-- 转换前Oracle SQL：
SELECT MAX(salary),MAX(1),MAX(DISTINCT department_id) FROM unisql_employee ke;
MAX(SALARY)|MAX(1)|MAX(DISTINCTDEPARTMENT_ID)|
-----------+------+--------------------------+
50000| 1| 3|
-- 转换后TDSQL-MySQL：
SELECT MAX(`salary`),MAX(1),MAX(DISTINCT `department_id`) FROM `unisql_employee` AS `ke`;
MAX(`salary`)|MAX(1)|MAX(DISTINCT `department_id`)|
-------------+------+-----------------------------+
50000| 1| 3|
5.2.10.10. MEDIAN
- 语法
TDSQL-MySQL不支持
5.2.10.11. MIN
- 语法
MIN({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]- 描述
- 该函数返回参数中指定列的最小值。可以将其用作聚合或分析函数。
说明
作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。
警告
统一SQL暂时不支持MIN作为分析函数的用法，不支持over子句。
参数解释
参数 |
说明 |
|---|---|
DISTINCT ALL |
计算时是否去重。为可选项，默认值 ALL。ALL： 计算所有值，包含重复行，且忽略值为 NULL 的行。DISTINCT： 去除重复行，且忽略值为 NULL 的行。 |
expr |
可为数值、字符、日期型或其它类型的数据列或表达式 |
OVER |
暂时不支持 |
示例
-- 转换前Oracle SQL：
SELECT MIN(SALARY),MIN(1),MIN(DISTINCT department_id) FROM unisql_employee
min |min|min|
----+---+---+
5000| 1| 1|
-- 转换后TDSQL-MySQL：
SELECT MIN(`SALARY`),MIN(1),MIN(DISTINCT `department_id`) FROM `unisql_employee`
MIN(`SALARY`)|MIN(1)|MIN(DISTINCT `department_id`)|
-------------+------+-----------------------------+
5000| 1| 1|
5.2.10.12. PERCENT_RANK
- 语法
TDSQL-MySQL不支持
5.2.10.13. RANK
- 语法
TDSQL-MySQL不支持
5.2.10.14. STDDEV
- 语法
TDSQL-MySQL不支持
5.2.10.15. STDDEV_POP
- 语法
TDSQL-MySQL不支持
5.2.10.16. STDDEV_SAMP
- 语法
TDSQL-MySQL不支持
5.2.10.17. SUM
- 语法
SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]- 描述
- 该函数返回指定参数的总和，可以将其用作聚合或分析函数。
说明
作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。
警告
统一SQL暂时不支持SUM作为分析函数的用法，不支持over子句。
参数解释
参数 |
说明 |
|---|---|
DISTINCT ALL |
计算时是否去重。为可选项，默认值 ALL。ALL： 计算所有值，包含重复行，且忽略值为 NULL 的行。DISTINCT： 去除重复行，且忽略值为 NULL 的行。 |
expr |
数值数据类型或任何可以隐式转换为数值数据类型的表达式。 |
OVER |
暂时不支持 |
示例
-- 转换前Oracle SQL：
SELECT sum(salary),sum(1),sum(DISTINCT department_id) FROM unisql_employee ke;
SUM(SALARY)|SUM(1)|SUM(DISTINCTDEPARTMENT_ID)|
-----------+------+--------------------------+
130000| 6| 6|
-- 转换后TDSQL-MySQL：
SELECT sum(`salary`),sum(1),sum(DISTINCT `department_id`) FROM `unisql_employee` AS `ke`.
sum(`salary`)|sum(1)|sum(DISTINCT `department_id`)|
-------------+------+-----------------------------+
130000| 6| 6|
5.2.10.18. VAR_POP
- 语法
TDSQL-MySQL不支持
5.2.10.19. VAR_SAMP
- 语法
TDSQL-MySQL不支持
5.2.10.20. VARIANCE
- 语法
TDSQL-MySQL不支持
5.2.11. 分析函数
暂不支持，建议业务调整
5.2.12. 其他
5.2.12.1. REGEXP_LIKE condition
- 语法
REGEXP_LIKE(source_char,pattern)- 描述
- 根据正则表达式进行匹配搜索
参数解释
参数 |
说明 |
|---|---|
source_char |
是一个字符表达式，可作为搜索值 |
pattern |
正则表达式 |
示例
-- 建表语句
CREATE TABLE unisql_regexp_like_test(id int,name varchar(50),hiredate varchar(50),salary int);
INSERT INTO unisql_regexp_like_test VALUES(100, 'RAPHAELY', '2017-07-01', 1700);
INSERT INTO unisql_regexp_like_test VALUES(100, 'DE HAAN', '2018-05-01',11000);
INSERT INTO unisql_regexp_like_test VALUES(100, 'ERRAZURIZ', '2017-07-21', 1400);
INSERT INTO unisql_regexp_like_test VALUES(100, 'HARTSTEIN', '2019-05-01',14000);
INSERT INTO unisql_regexp_like_test VALUES(100, 'RAPHAELY', '2017-07-22', 1700);
INSERT INTO unisql_regexp_like_test VALUES(100, 'WEISS', '2019-07-11',13500);
INSERT INTO unisql_regexp_like_test VALUES(100, 'RUSSELL', '2019-10-05', 13000);
INSERT INTO unisql_regexp_like_test VALUES(100, 'PARTNERS', '2018-12-01',14000);
INSERT INTO unisql_regexp_like_test VALUES(200, 'ROSS', '2019-06-11',13500);
INSERT INTO unisql_regexp_like_test VALUES(200, 'BELL', '2019-05-25', 13000);
INSERT INTO unisql_regexp_like_test VALUES(200, 'PART', '2018-08-11',14000);
-- 转换前Oracle SQL：
SELECT * FROM unisql_regexp_like_test WHERE regexp_like(name, '^R')
ID |NAME |HIREDATE |SALARY|
---+--------+----------+------+
100|RAPHAELY|2017-07-01| 1700|
100|RAPHAELY|2017-07-22| 1700|
100|RUSSELL |2019-10-05| 13000|
200|ROSS |2019-06-11| 13500|
-- 转换后TDSQL-MySQL：
SELECT * FROM `unisql_regexp_like_test` WHERE `name` REGEXP '^R'
id |name |hiredate |salary|
---+--------+----------+------+
100|RAPHAELY|2017-07-01| 1700|
100|RAPHAELY|2017-07-22| 1700|
100|RUSSELL |2019-10-05| 13000|
200|ROSS |2019-06-11| 13500|
5.2.13. 注意事项
暂不支持分析函数。
暂不支持over子句。
关于参数中涉及到字面量
''
oracle对字面量''的处理等价于NULLmysql对字面量''的处理等价于0,在使用过程中，可能会出现两边结果不一致的情况在单独使用字面量''时请注意，举例说明：
-- 转换前Oracle SQL：
SELECT EXP(NULL),EXP(''),EXP(0) FROM DUAL;
---------+-------+------+
EXP(NULL)|EXP('')|EXP(0)|
---------+-------+------+
| | 1|
-- 转换后TDSQL-MySQL：
SELECT EXP(NULL),EXP(''),EXP(0) FROM DUAL;
---------+-------+------+
EXP(NULL)|EXP('')|EXP(0)|
---------+-------+------+
| 1.0| 1.0|
对于EXTRACT函数
Oracle在进行时间抽取时,如果参数带有时区,则根据其对应的UTC时间进行抽取,如果是其他类型的时间字段,则根据GMT时间进行抽取。MySQL在进行时间抽取时,都是按照GMT对应时间进行抽取,即不考虑时区。
-- execute in oracle, you can see the difference between the result value of hour
SELECT CURRENT_TIMESTAMP,systimestamp,TO_TIMESTAMP_TZ('2023-09-07 16:15:40','YYYY-MM-DD HH24:MI:SS'),TIMESTAMP '2023-09-07 16:15:40' FROM dual
-----------------------------+-----------------------------+------------------------------------------------------------+-----------------------------+
CURRENT_TIMESTAMP |SYSTIMESTAMP |TO_TIMESTAMP_TZ('2023-09-0716:15:40','YYYY-MM-DDHH24:MI:SS')|TIMESTAMP'2023-09-0716:15:40'|
-----------------------------+-----------------------------+------------------------------------------------------------+-----------------------------+
2023-09-07 16:51:27.192 +0800|2023-09-07 16:51:27.192 +0800| 2023-09-07 16:15:40.000 +0800| 2023-09-07 16:15:40.000|
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS HOUR1,EXTRACT(HOUR FROM systimestamp) AS HOUR2,EXTRACT(HOUR FROM TO_TIMESTAMP_TZ('2023-09-07 16:15:40','YYYY-MM-DD HH24:MI:SS')) AS HOUR3,EXTRACT(HOUR FROM TIMESTAMP '2023-09-07 16:15:40') AS HOUR FROM DUAL
-----+-----+-----+----+
HOUR1|HOUR2|HOUR3|HOUR|
-----+-----+-----+----+
8| 8| 8| 16|
关于ROWNUM
rownum作为where条件时,目前只支持=1和<=num两种写法,参考rownum的示例。
关于自定义函数
由于Oracle中一些函数在MySQL中没有与之对应的实现,统一SQL工具为尽可能的进行支持,提供了一些自定义函数,序列等的实现,在使用时需要先在MySQL数据库中运行统一SQL提供的脚本。
常用日期格式模型列表
日期格式，说明 |
|
|---|---|
YYYY |
4位数字表示的年份（例如：2021）。 |
YYY |
3位数字表示的年份（例如：021）。 |
YY |
2位数字表示的年份（例如：21）。 |
Y |
年份的最后一位或两位数字（例如：1，21）。 |
RRRR |
四位数字表示的年份，可以自动解释为近期年份（例如：2021）。 |
Q |
一年中的季度（1-4）。 |
MM |
表示月份的两位数字（01-12）。 |
MON |
缩写形式的月份名称（例如：JAN，FEB）。 |
MONTH |
完整形式的月份名称（例如：JANUARY，FEBRUARY）。 |
DD |
表示月份中的日期的两位数字（01-31）。 |
DY |
缩写形式的星期几名称（例如：MON，TUE）。 |
DAY |
完整形式的星期几名称（例如：MONDAY，TUESDAY）。 |
HH |
小时（00-23）。 |
HH12 |
12小时制的小时（01-12）。 |
HH24 |
24小时制的小时（00-23）。 |
MI |
分钟（00-59）。 |
SS |
秒（00-59）。 |
FF |
小数秒（精度为百万分之一秒）。 |
AM |
上午/下午标识符（例如：AM，PM）。 |