Oracle兼容-函数-TO_NUMBER()函数

1. 语法

  1. TO_NUMBER(expr [ , fmt [ , 'nlsparam'] ] )

2. 定义和用法

TO_NUMBER 将 expr 转换成一个由 fmt 指定格式的 number 类型的值。

当前支持用法：

  1. -- 最简语法
  2. TO_NUMBER(expr)
  4. -- 自动去除左右空格
  5. TO_NUMBER(' xxxxx ')
  7. -- 目前fmt参数仅支持常用如"9990.9909"或'9990.9909'格式，整数位以及小数位支持9或0表示格式，不进行四舍五入操作
  8. TO_NUMBER(expr [ , fmt [ , 'nlsparam'] ] )
  10. -- 以下两个SQL都返回NULL
  11. TO_NUMBER('')
  12. TO_NUMBER(null)
  14. -- 完整语法
  15. TO_NUMBER(expr [ , fmt [ , 'nlsparam'] ] )

参数 fmt 可选值如下表所示：

fmt序号格式符含义举例
19返回具有指定位数的值，如果是正数则带有前导空格，如果是负数则带有前导减号。前导零是空白，除了零值，它为定点数的整数部分返回零TO_NUMBER(‘1234’,’9999’) 返回 1234
20前导零， 尾随0TO_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
5EEEE返回科学计数算法的值(当为‘+’，指数<15,结果显示数字，>=15以后结果显示科学计算数据)；当为‘-’，指数<16,结果显示数字，>=16以后结果显示科学计算数据)TO_NUMBER(‘1.6E+02’,’9.9EEEE’) 返回 160；TO_NUMBER(‘1.6e+16’,’9.9EEEE’) 返回 1.6e16
6X十六进制转换为10进制的数TO_NUMBER(‘4D2’,’XXX’) 返回 1234

3. Oracle兼容说明

  1. 目前支持的 fmt 格式列在上方表格中，其它暂时未支持的格式会触发报错提示。

  2. 科学计数法(EEEE, fmt = 5)格式输出结果与Oracle有差异，执行SQL语句 SELECT TO_NUMBER('1.666e+30','9.999EEEE') FROM DUAL; 在Oracle 得到的结果为 1.666e+30，GreatSQL得到的结果为：1.666e30。

  3. 当设定为 X 格式（fmt = 6），且输入参数值包含特殊字符如 ‘.’ 时，TO_NUMBER(',0.','xxx') 结果与Oracle有差异，Oracle返回一个很大的数值，而GreatSQL判定为fmt格式输入不匹配发出报错。

  4. 执行例如 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。

  5. 转义符号 \ 在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. 示例

  1. greatsql> SELECT TO_NUMBER('1234.56');
  2. +----------------------+
  3. | TO_NUMBER('1234.56') |
  4. +----------------------+
  5. |              1234.56 |
  6. +----------------------+
  8. greatsql> SHOW CREATE TABLE t1\G
  9. *************************** 1. row ***************************
  10.        Table: t1
  11. Create Table: CREATE TABLE `t1` (
  12.   `id` int unsigned NOT NULL AUTO_INCREMENT,
  13.   `c1` int unsigned DEFAULT NULL,
  14.   `c2` varchar(10) NOT NULL,
  15.   PRIMARY KEY (`id`)
  16. ) ENGINE=InnoDB;
  18. greatsql> SELECT * FROM t1;
  19. +----+------+----------+
  20. | id | c1   | c2       |
  21. +----+------+----------+
  22. |  1 |  211 | 1234abca |
  23. |  2 | NULL | 1234     |
  24. +----+------+----------+
  26. greatsql> SELECT id, c2, TO_NUMBER(c2) FROM t1 WHERE id = 2;
  27. +----+------+---------------+
  28. | id | c2   | TO_NUMBER(c2) |
  29. +----+------+---------------+
  30. |  2 | 1234 |          1234 |
  31. +----+------+---------------+
  33. greatsql> SELECT id, c2, TO_NUMBER(c2, "999999.999") FROM t1 WHERE id = 2;
  34. +----+------+-----------------------------+
  35. | id | c2   | TO_NUMBER(c2, "999999.999") |
  36. +----+------+-----------------------------+
  37. |  2 | 1234 |                    1234.000 |
  38. +----+------+-----------------------------+
  40. -- 输入值不符合要求，超出转换范围
  41. greatsql>  SELECT id, c2, TO_NUMBER(c2, "999999") FROM t1 WHERE id = 1;
  42. ERROR 1690 (22003): NUMBER value is out of range in 'to_number'
  44. -- fmt参数格式错误
  45. greatsql> SELECT TO_NUMBER(123.456, 'a999.99') ;
  46. ERROR 1525 (HY000): Incorrect format model value: 'a999.99'
  48. -- fmt参数格式正确，但来源数据不符合格式
  49. greatsql> SELECT TO_NUMBER(123333.456, '999.99') ;
  50.   ERROR 1690 (22003): NUMBER value is out of range in 'to_number'
  52. -- fmt格式参数超出范围
  53. greatsql> SELECT TO_NUMBER(12333333333333333333333333333333333333333.456,'9999999999999999999999999999999999999999999999999999999999999999999999.99') ;
  54. ERROR 1059 (42000): Identifier name '9999999999999999999999999999999999999999999999999999999999999999999999.99' is too long
  56. greatsql> SELECT TO_NUMBER('12,12,12','99,99,99.99') FROM DUAL;
  57. +-------------------------------------+
  58. | TO_NUMBER('12,12,12','99,99,99.99') |
  59. +-------------------------------------+
  60. |                              121212 |
  61. +-------------------------------------+
  63. greatsql> SELECT TO_NUMBER('0.12','0.99') FROM DUAL;
  64. +--------------------------+
  65. | TO_NUMBER('0.12','0.99') |
  66. +--------------------------+
  67. |                     0.12 |
  68. +--------------------------+
  70. greatsql> SELECT TO_NUMBER('1.6E+02','9.9EEEE') FROM DUAL;
  71. +--------------------------------+
  72. | TO_NUMBER('1.6E+02','9.9EEEE') |
  73. +--------------------------------+
  74. |                            160 |
  75. +--------------------------------+
  77. greatsql> SELECT TO_NUMBER('1.6e+16','9.9EEEE') FROM DUAL;
  78. +--------------------------------+
  79. | TO_NUMBER('1.6e+16','9.9EEEE') |
  80. +--------------------------------+
  81. |                         1.6e16 |
  82. +--------------------------------+
  84. greatsql> SELECT TO_NUMBER('f12','XXX') FROM DUAL;
  85. +------------------------+
  86. | TO_NUMBER('f12','XXX') |
  87. +------------------------+
  88. |                   3858 |
  89. +------------------------+
  91. greatsql> SELECT TO_NUMBER('4D2','XXX') FROM DUAL;
  92. +------------------------+
  93. | TO_NUMBER('4D2','XXX') |
  94. +------------------------+
  95. |                   1234 |
  96. +------------------------+
  98. greatsql> SET sql_mode = NO_BACKSLASH_ESCAPES;
  100. greatsql> SELECT TO_NUMBER('-\,0.0','9,9.9') FROM DUAL;
  101. ERROR 1690 (22003): NUMBER value is out of range in 'to_number'
  103. greatsql> SET sql_mode = DEFAULT;
  105. greatsql> SELECT TO_NUMBER('-\,0.0','9,9.9') FROM DUAL;
  106. +-----------------------------+
  107. | TO_NUMBER('-\,0.0','9,9.9') |
  108. +-----------------------------+
  109. |                           0 |
  110. +-----------------------------+

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx