摘要:本文主要向大家介绍了Oracle数据库之oracle的TO_NUMBER函数,通过具体的内容向大家展现,希望对大家学习Oracle数据库有所帮助。
本文主要向大家介绍了Oracle数据库之oracle的TO_NUMBER函数,通过具体的内容向大家展现,希望对大家学习Oracle数据库有所帮助。
TO_NUMBER(x [, format], [ nls_language ]) converts x to a NUMBER.
x is the string that will be converted to a number. x是将要被转换成number的字符串。
format, optional, is the format that will be used to convert x to a number. format,可选项,是用来将x转换成number的格式。
nls_language, optional, is the nls language used to convert x to a number. nls_language,可选项,是nls语言,用来将x转换成number。
Number Format Elements:
ElementExampleDescription
, .
1,234.56
Commas and decimal points 逗号和小数点
$
$123.45
Leading dollar sign. 前置$符号
0
0012.34
Leading or trailing 0. 前置或后置0
9
123
Any digit. 任意数字
B
B123
Leading blank for integers. integer类型前置空白符
C
C123
The ISO currency symbol defined in the NLS_ISO_CURRENCY parameter.定义在NLS_ISO_CURRENCY参数中的国际标准货币符号
D
123D99
The current decimal character defined in the NLS_NUMERIC_CHARACTERS parameter. The default value is a period.
EEEE
1.2EEE
Returns a value in scientific notation.用科学计数法表示返回值
G
9G123
Returns the group separator (e.g., a comma).
L
L123
Returns the local currency symbol.
MI
123MI
negative value with trailing minus sign; returns positive value with a trailing blank.
PR
123PR
The negative values in angle brackets.
RN
I
values in Roman numerals, uppercase.
rn
i
values in Roman numerals, lowercase.
S (prefix)
S1234
negative values with a leading minus sign, positive values with a leading positive sign.
S (suffix)
1234S
negative values with a trailing minus sign, positive values with a trailing positive sign.
TM
TM
The text minimum number format model returns the smallest number of characters possible.
U
U123
the Euro currency symbol or the NLS_DUAL_CURRENCY parameter.
V
123V99
a value multiplied by 10n, where n is the number of 9s after the V.
X
XXXX
the hexadecimal value.十六进制值
SQL> select to_number(‘123.45‘) from dual;
TO_NUMBER(‘123.45‘)
-------------------
123.45
SQL> select to_number(‘123.45‘, ‘9999.99‘) from dual;
TO_NUMBER(‘123.45‘,‘9999.99‘)
-----------------------------
123.45
SQL> select to_number(‘12‘, ‘99‘) from dual;
TO_NUMBER(‘12‘,‘99‘)
--------------------
12
SELECT TO_NUMBER(‘$12,123.23‘,‘$999,999.99‘) FROM DUAL;
SQL>
SQL>
SQL>
SQL>
Do calculation after the conversion
SQL> select to_number(‘123.45‘) + 2 from dual;
TO_NUMBER(‘123.45‘)+2
---------------------
125.45
SQL> SELECT TO_NUMBER(‘-$12,345.67‘, ‘$99,999.99‘) FROM dual;
TO_NUMBER(‘-$12,345.67‘,‘$99,999.99‘)
-------------------------------------
-12345.67
SQL>
本文由职坐标整理并发布,希望对同学们学习Oracle有所帮助,更多内容请关注职坐标数据库Oracle数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号