§ Oracle兼容-函数-TO_NUMBER()函数
§ 1. 语法
TO_NUMBER(expr [ , fmt [ , 'nlsparam'] ] )
§ 2. 定义和用法
TO_NUMBER 将 expr 转换成一个由 fmt 指定格式的 number 类型的值。
当前支持用法：
-- 最简语法
TO_NUMBER(expr)
-- 自动去除左右空格
TO_NUMBER(' xxxxx ')
-- 目前fmt参数仅支持常用如"9990.9909"或'9990.9909'格式，整数位以及小数位支持9或0表示格式，不进行四舍五入操作
TO_NUMBER(expr [ , fmt [ , 'nlsparam'] ] )
-- 以下两个SQL都返回NULL
TO_NUMBER('')
TO_NUMBER(null)
-- 完整语法
TO_NUMBER(expr [ , fmt [ , 'nlsparam'] ] )
参数 fmt 可选值如下表所示：
| fmt序号 | 格式符 | 含义 | 举例 |
|---|---|---|---|
| 1 | 9 | 返回具有指定位数的值，如果是正数则带有前导空格，如果是负数则带有前导减号。前导零是空白，除了零值，它为定点数的整数部分返回零 | TO_NUMBER('1234','9999') 返回 1234 |
| 2 | 0 | 前导零， 尾随0 | TO_NUMBER('0234','0999') 返回 234；参数整数部分必须4位，待转换字符串高位以0补充 |
| 3 | 逗号, | 字符串中含逗号的转换，格式模型中可指定多个逗号，格式限制为：逗号不能在开端；逗号不能出现在小数点右边 | TO_NUMBER('1,234.567','9,999.999') 返回 1234.567 |
| 4 | 小数. | 小数格式转换, 它是指定小数点的位置 | TO_NUMBER('0.12','0.99') 返回 0.12 |
| 5 | EEEE | 返回科学计数算法的值(当为‘+’，指数<15,结果显示数字，>=15以后结果显示科学计算数据)；当为‘-’，指数<16,结果显示数字，>=16以后结果显示科学计算数据) | TO_NUMBER('1.6E+02','9.9EEEE') 返回 160；TO_NUMBER('1.6e+16','9.9EEEE') 返回 1.6e16 |
| 6 | X | 十六进制转换为10进制的数 | TO_NUMBER('4D2','XXX') 返回 1234 |
§ 3. Oracle兼容说明
目前支持的
fmt格式列在上方表格中，其它暂时未支持的格式会触发报错提示。科学计数法(EEEE, fmt = 5)格式输出结果与Oracle有差异，执行SQL语句
SELECT TO_NUMBER('1.666e+30','9.999EEEE') FROM DUAL;在Oracle 得到的结果为 1.666e+30，GreatSQL得到的结果为：1.666e30。当设定为 X 格式（fmt = 6），且输入参数值包含特殊字符如 ‘.’ 时，
TO_NUMBER(',0.','xxx')结果与Oracle有差异，Oracle返回一个很大的数值，而GreatSQL判定为fmt格式输入不匹配发出报错。执行例如
TO_NUMBER(',123,','99,999,')时，结果与Oracle有差异，Oracle中会报错，GreatSQL不会报错。目前与已知兼容场景冲突，如：TO_NUMBER(',123','9,9,9,999')及TO_NUMBER('123,','9,9,9,999,')，在GreatSQL中都不会报错，这几个例子在GreatSQL中都是返回 123。转义符号
\在GreatSQL中会被统一过滤处理，与Oracle有差异。只有当设置sql_mode = NO_BACKSLASH_ESCAPES时，GreatSQL不会过滤转义字符，这时就与Oracle行为一致了。例如：SET sql_mode = NO_BACKSLASH_ESCAPES; SELECT TO_NUMBER('-\,0.0','9,9.9') FROM DUAL;，会提示错误，与Oracle行为一致。
§ 4. 示例
greatsql> SELECT TO_NUMBER('1234.56');
+----------------------+
| TO_NUMBER('1234.56') |
+----------------------+
| 1234.56 |
+----------------------+
greatsql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`c1` int unsigned DEFAULT NULL,
`c2` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
greatsql> SELECT * FROM t1;
+----+------+----------+
| id | c1 | c2 |
+----+------+----------+
| 1 | 211 | 1234abca |
| 2 | NULL | 1234 |
+----+------+----------+
greatsql> SELECT id, c2, TO_NUMBER(c2) FROM t1 WHERE id = 2;
+----+------+---------------+
| id | c2 | TO_NUMBER(c2) |
+----+------+---------------+
| 2 | 1234 | 1234 |
+----+------+---------------+
greatsql> SELECT id, c2, TO_NUMBER(c2, "999999.999") FROM t1 WHERE id = 2;
+----+------+-----------------------------+
| id | c2 | TO_NUMBER(c2, "999999.999") |
+----+------+-----------------------------+
| 2 | 1234 | 1234.000 |
+----+------+-----------------------------+
-- 输入值不符合要求，超出转换范围
greatsql> SELECT id, c2, TO_NUMBER(c2, "999999") FROM t1 WHERE id = 1;
ERROR 1690 (22003): NUMBER value is out of range in 'to_number'
-- fmt参数格式错误
greatsql> SELECT TO_NUMBER(123.456, 'a999.99') ;
ERROR 1525 (HY000): Incorrect format model value: 'a999.99'
-- fmt参数格式正确，但来源数据不符合格式
greatsql> SELECT TO_NUMBER(123333.456, '999.99') ;
ERROR 1690 (22003): NUMBER value is out of range in 'to_number'
-- fmt格式参数超出范围
greatsql> SELECT TO_NUMBER(12333333333333333333333333333333333333333.456,'9999999999999999999999999999999999999999999999999999999999999999999999.99') ;
ERROR 1059 (42000): Identifier name '9999999999999999999999999999999999999999999999999999999999999999999999.99' is too long
greatsql> SELECT TO_NUMBER('12,12,12','99,99,99.99') FROM DUAL;
+-------------------------------------+
| TO_NUMBER('12,12,12','99,99,99.99') |
+-------------------------------------+
| 121212 |
+-------------------------------------+
greatsql> SELECT TO_NUMBER('0.12','0.99') FROM DUAL;
+--------------------------+
| TO_NUMBER('0.12','0.99') |
+--------------------------+
| 0.12 |
+--------------------------+
greatsql> SELECT TO_NUMBER('1.6E+02','9.9EEEE') FROM DUAL;
+--------------------------------+
| TO_NUMBER('1.6E+02','9.9EEEE') |
+--------------------------------+
| 160 |
+--------------------------------+
greatsql> SELECT TO_NUMBER('1.6e+16','9.9EEEE') FROM DUAL;
+--------------------------------+
| TO_NUMBER('1.6e+16','9.9EEEE') |
+--------------------------------+
| 1.6e16 |
+--------------------------------+
greatsql> SELECT TO_NUMBER('f12','XXX') FROM DUAL;
+------------------------+
| TO_NUMBER('f12','XXX') |
+------------------------+
| 3858 |
+------------------------+
greatsql> SELECT TO_NUMBER('4D2','XXX') FROM DUAL;
+------------------------+
| TO_NUMBER('4D2','XXX') |
+------------------------+
| 1234 |
+------------------------+
greatsql> SET sql_mode = NO_BACKSLASH_ESCAPES;
greatsql> SELECT TO_NUMBER('-\,0.0','9,9.9') FROM DUAL;
ERROR 1690 (22003): NUMBER value is out of range in 'to_number'
greatsql> SET sql_mode = DEFAULT;
greatsql> SELECT TO_NUMBER('-\,0.0','9,9.9') FROM DUAL;
+-----------------------------+
| TO_NUMBER('-\,0.0','9,9.9') |
+-----------------------------+
| 0 |
+-----------------------------+
§ 问题反馈
§ 联系我们
扫码关注微信公众号
