to_number() ----函数的用法

1 用法简介

TO_NUMBER函数()是Oracle中常用的类型转换函数之一，主要是将字符串转换为数值型的格式，与TO_CHAR()函数的作用正好相反。

To_number函数的格式如下：

<code class="language-SQL hljs livecodeserver has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">To_number（varchar2 <span class="hljs-operator" style="box-sizing: border-box;">or</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">char</span>,’<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">format</span> model’）</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

To_number函数中也有很多预定义的固定格式：

格式值	含义
9	代表一个数字
0	强迫0显示
$	显示美元符号
L	强制显示一个当地的货币符号
.	显示一个小数点
,	显示一个千位分隔符号

2 一些例子

<code class="language-SQL hljs cs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">SQL> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> to_number（’RMB234234<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.4350</span>′，’L999999<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.0000</span>′） <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> dual;
    TO_NUMBER（’RMB234234<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.4350</span>′，’L999999<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.0000</span>′）
    ——————————————
    <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">234234.435</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li></ul>

<code class="language-SQL hljs perl has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">SQL> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> to_number（’<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$1</span>23,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">233</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">455</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">623.3400</span>′，’<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$9</span>99,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">999</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">999</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">999.0000</span>′） from dual;
    TO_NUMBER（’<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$1</span>23,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">233</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">455</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">623.3400</span>′，’<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$9</span>99,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">999</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">999</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">999.0000</span>′）
    ———————————————————-
    <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1.2323</span>E+<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">11</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li></ul>

3 用法陷阱

有的时候你会发现，使用了TO_NUMBER()函数并且语法正确，但是Oracle却报“invalid number”的错误，而你在一遍又一遍认认真真检查并确定语句无误之后大呼惊奇，以为TO_NUMBER()函数还有什么可能不知道的用法。其实这很可能是你所查询的数据出现了问题，而非SQL。使用TO_NUMBER()函数的时候，一定要确保所转换字段是可转换为数字的，比如字符串“20151008”是可以转换为数字20151008的，但是字符串“2015-10-08”不可以。如果你的字段中包含了字符串“2015-10-08”，而你还直接使用了TO_NUMBER()函数进行操作的话就会报“invalid number”的错。

4 逃出陷阱

如何逃出陷阱呢？

1 前端校验

尽量在用户输入的时候进行必要的校验，确保输入的数值就是我们需要的格式。

2 后台校验

在后台代码中进行必要的检查，筛选到错误的值并且传给前台合理的提示。

3 SQL校验

一旦你的数值进了数据库，问题就变得复杂了。还有一种情况是，数据是历史的，我们不能修改它，但是还要进行必要的查询。这时候就需要在SQL层面做点什么了。假设需要使用TO_NUMBER()函数的字段是varchar2类型的名为“status”。比如，如果非法数据都是比较长的，你可以在SQL中加上长度的校验，即多加一个where条件：

<code class="language-SQL hljs fix has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-attribute" style="box-sizing: border-box;">LENGTH(status) <</span>=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;"> 10</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

或者，也可以将非法字符替换掉，，同样是在where中多加一个条件：

<code class="language-SQL hljs scss has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-function" style="box-sizing: border-box;">TO_NUMBER(<span class="hljs-function" style="box-sizing: border-box;">REGEXP_REPLACE(status,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'[^0-9]'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">''</span>)</span>)</span> > 30</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

实例中REGEXP_REPLACE()将“status”字段中所有非数字的字符替换为空字符，然后再用TO_NUMBER()进行比较就可以啦！

regexp_replace()

----------------------------------------------------------------------

REGEXP_REPLACE 函数 

让我们首先看一下传统的 REPLACE SQL 函数，它把一个字符串用另一个字符串来替换。假设您的数据在正文中有不必要的空格，您希望用单个空格来替换它们。利用 REPLACE 函数，您需要准确地列出您要替换多少个空格。然而，多余空格的数目在正文的各处可能不是相同的。下面的示例在 Joe 和 Smith 之间有三个空格。REPLACE 函数的参数指定要用一个空格来替换两个空格。在这种情况下，结果在原来的字符串的 Joe 和 Smith 之间留下了一个额外的空格。 

SELECT REPLACE('Joe Smith',' ', ' ')
AS replace
FROM dual
REPLACE
---------
Joe Smith 

REGEXP_REPLACE 函数把替换功能向前推进了一步，其语法在表 9 中列出。以下查询用单个空格替换了任意两个或更多的空格。( ) 子表达式包含了单个空格，它可以按 {2,} 的指示重复两次或更多次。 

表 9： REGEXP_REPLACE 函数  

语法	说明
REGEXP_REPLACE(source_string, pattern [, replace_string [, position [,occurrence, [match_parameter]]]])	该函数用一个指定的 replace_string 来替换匹配的模式，从而允许复杂的"搜索并替换"操作。

SELECT REGEXP_REPLACE('Joe Smith',
'( ){2,}', ' ')
AS RX_REPLACE
FROM dual
RX_REPLACE
----------
Joe Smith 

后向引用  正则表达式的一个有用的特性是能够存储子表达式供以后重用；这也被称为后向引用（在表 10 中对其进行了概述）。它允许复杂的替换功能，如在新的位置上交换模式或显示重复出现的单词或字母。子表达式的匹配部分保存在临时缓冲区中。缓冲区从左至右进行编号，并利用 \digit 符号进行访问，其中 digit 是 1 到 9 之间的一个数字，它匹配第 digit 个子表达式，子表达式用一组圆括号来显示。  接下来的例子显示了通过按编号引用各个子表达式将姓名 Ellen Hildi Smith 转变为 Smith, Ellen Hildi。  SELECT REGEXP_REPLACE( 'Ellen Hildi Smith', '(.*) (.*) (.*)', '\3, \1 \2') FROM dual REGEXP_REPLACE('EL ------------------ Smith, Ellen Hildi  该 SQL 语句显示了用圆括号括住的三个单独的子表达式。每一个单独的子表达式包含一个匹配元字符 (.)，并紧跟着 * 元字符，表示任何字符（除换行符之外）都必须匹配零次或更多次。空格将各个子表达式分开，空格也必须匹配。圆括号创建获取值的子表达式，并且可以用 \digit 来引用。第一个子表达式被赋值为 \1 ，第二个 \2，以此类推。这些后向引用被用在这个函数的最后一个参数 (\3, \1 \2) 中，这个函数有效地返回了替换子字符串，并按期望的格式来排列它们（包括逗号和空格）。表 11 详细说明了该正则表达式的各个组成部分。  后向引用对替换、格式化和代替值非常有用，并且您可以用它们来查找相邻出现的值。接下来的例子显示了使用 REGEP_SUBSTR 函数来查找任意被空格隔开的重复出现的字母数字值。显示的结果给出了识别重复出现的单词 is 的子字符串。  SELECT REGEXP_SUBSTR( 'The final test is is the implementation', ([[:alnum:]]+)([[:space:]]+)\1') AS substr FROM dual SUBSTR ------ is is  匹配参数选项  您可能已经注意到了正则表达式操作符和函数包含一个可选的匹配参数。这个参数控制是否区分大小写、换行符的匹配和保留多行输入。  正则表达式的实际应用  您不仅可以在队列中使用正则表达式，还可以在使用 SQL 操作符或函数的任何地方（比如说在 PL/SQL 语言中）使用正则表达式。您可以编写利用正则表达式功能的触发器，以验证、生成或提取值。  接下来的例子演示了您如何能够在一次列检查约束条件中应用 REGEXP_LIKE 操作符来进行数据验证。它在插入或更新时检验正确的社会保险号码格式。如 123-45-6789 和 123456789 之类格式的社会保险号码对于这种列约束条件是可接受的值。有效的数据必须以三个数字开始，紧跟着一个连字符，再加两个数字和一个连字符，最后又是四个数字。另一种表达式只允许 9 个连续的数字。竖线符号 (|) 将各个选项分开。  ALTER TABLE students   ADD CONSTRAINT stud_ssn_ck CHECK   (REGEXP_LIKE(ssn,   '^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$')) 由 ^ 和 $ 指示的开头或结尾的字符都是不可接受的。确保您的正则表达式没有分成多行或包含任何不必要的空格，除非您希望格式如此并相应地进行匹配。表 12 说明了该正则表达式示例的各个组成部分。  将正则表达式与现有的功能进行比较  正则表达式有几个优点优于常见的 LIKE 操作符和 INSTR、SUBSTR 及 REPLACE 函数的。这些传统的 SQL 函数不便于进行模式匹配。只有 LIKE 操作符通过使用 % 和 _ 字符匹配，但 LIKE 不支持表达式的重复、复杂的更替、字符范围、字符列表和 POSIX 字符类等等。此外，新的正则表达式函数允许检测重复出现的单词和模式交换。这里的例子为您提供了正则表达式领域的一个概览，以及您如何能够在您的应用程序中使用它们。  实实在在地丰富您的工具包  因为正则表达式有助于解决复杂的问题，所以它们是非常强大的。正则表达式的一些功能难于用传统的 SQL 函数来仿效。当您了解了这种稍显神秘的语言的基础构建程序块时，正则表达式将成为您的工具包的不可缺少的一部分（不仅在 SQL 环境下也在其它的编程语言环境下）。为了使您的各个模式正确，虽然尝试和错误有时是必须的，但正则表达式的简洁和强大是不容置疑的。  Alice Rischert (ar280@yahoo.com) 是哥伦比亚大学计算机技术与应用系的数据库应用程序开发和设计方向的主席。她编写了 Oracle SQL 交互手册 第 2 版 （Prentice Hall，2002）和即将推出的 Oracle SQL 示例 （Prentice Hall，2003）。Rischert 拥有超过 15 年的经验在财富 100 强公司内担任数据库设计师、DBA 和项目主管，并且她自从 Oracle version 5 起就一直使用 Oracle 产品。  表 1：定位元字符   元字符 说明 ^ 使表达式定位至一行的开头 $ 使表达式定位至一行的末尾  表 2：量词或重复操作符   量词 说明 * 匹配 0 次或更多次 ? 匹配 0 次或 1 次 + 匹配 1 次或更多次 {m} 正好匹配 m 次 {m,} 至少匹配 m 次 {m, n} 至少匹配 m 次但不超过 n 次 表 3：预定义的 POSIX 字符类   字符类 说明 [:alpha:] 字母字符 [:lower:] 小写字母字符 [:upper:] 大写字母字符 [:digit:] 数字 [:alnum:] 字母数字字符 [:space:] 空白字符（禁止打印），如回车符、换行符、竖直制表符和换页符 [:punct:] 标点字符 [:cntrl:] 控制字符（禁止打印） [:print:] 可打印字符 表 4：表达式的替换匹配和分组   元字符 说明 | 替换 分隔替换选项，通常与分组操作符 () 一起使用 ( ) 分组 将子表达式分组为一个替换单元、量词单元或后向引用单元（参见"后向引用"部分） [char] 字符列表 表示一个字符列表；一个字符列表中的大多数元字符（除字符类、^ 和 - 元字符之外）被理解为文字 表 5：REGEXP_LIKE 操作符   语法 说明 REGEXP_LIKE(source_string, pattern [, match_parameter]) source_string 支持字符数据类型（CHAR、VARCHAR2、CLOB、NCHAR、NVARCHAR2 和 NCLOB，但不包括 LONG）。pattern 参数是正则表达式的另一个名称。match_parameter 允许可选的参数（如处理换行符、保留多行格式化以及提供对区分大小写的控制）。 表 6：REGEXP_INSTR 函数   语法 说明 REGEXP_INSTR(source_string, pattern [, start_position [, occurrence [, return_option [, match_parameter]]]]) 该函数查找 pattern ，并返回该模式的第一个位置。您可以随意指定您想要开始搜索的 start_position。 occurrence 参数默认为 1，除非您指定您要查找接下来出现的一个模式。return_option 的默认值为 0，它返回该模式的起始位置；值为 1 则返回符合匹配条件的下一个字符的起始位置。 表 7： 5 位数字加 4 位邮政编码表达式的说明   语法 说明   必须匹配的空白 [:digit:] POSIX 数字类  ] 字符列表的结尾  {5} 字符列表正好重复出现 5 次 ( 子表达式的开头  - 一个文字连字符，因为它不是一个字符列表内的范围元字符 [ 字符列表的开头  [:digit:] POSIX [:digit:]类 [ 字符列表的开头  ] 字符列表的结尾 {4} 字符列表正好重复出现 4 次  ) 结束圆括号，结束子表达式  ? ? 量词匹配分组的子表达式 0 或 1 次，从而使得 4 位代码可选 $ 定位元字符，指示行尾 表 8：REGEXP_SUBSTR 函数   语法 说明 REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, match_parameter]]]) REGEXP_SUBSTR 函数返回匹配模式的子字符串。 表 9： REGEXP_REPLACE 函数   语法 说明 REGEXP_REPLACE(source_string, pattern [, replace_string [, position [,occurrence, [match_parameter]]]]) 该函数用一个指定的 replace_string 来替换匹配的模式，从而允许复杂的"搜索并替换"操作。 表 10：后向引用元字符   元字符 说明 \digit 反斜线 紧跟着一个 1 到 9 之间的数字，反斜线匹配之前的用括号括起来的第 digit 个子表达式。 （注意：反斜线在正则表达式中有另一种意义，取决于上下文，它还可能表示 Escape 字符。 表 11：模式交换正则表达式的说明   正则表达式项目 说明 ( 第一个子表达式的开头  . 匹配除换行符之外的任意单字符 * 重复操作符，匹配之前的 . 元字符 0 到 n 次 ) 第一个子表达式的结尾；匹配结果在 \1 中获取（在这个例子中，结果为 Ellen。）   必须存在的空白  ( 第二个子表达式的开头  . 匹配除换行符之外的任意单个字符  * 重复操作符，匹配之前的 . 元字符 0 到 n 次 ) 第二个子表达式的结尾；匹配结果在 \2 中获取（在这个例子中，结果为 Hildi。）   空白  ( 第三个子表达式的开头 tr>  . 匹配除换行符之外的任意单字符  * 重复操作符，匹配之前的 . 元字符 0 到 n 次 ) 第三个子表达式的结尾；匹配结果在 \3 中获取（在这个例子中，结果为 Smith。） 表 12：社会保险号码正则表达式的说明   正则表达式项目 说明 ^ 行首字符（正则表达式在匹配之前不能有任何前导字符。） ( 开始子表达式并列出用 | 元字符分开的可替换选项 [ 字符列表的开头 [:digit:] POSIX 数字类  ] 字符列表的结尾  {3} 字符列表正好重复出现 3 次  - 连字符  [ 字符列表的开头  [:digit:] POSIX 数字类 ] 字符列表的结尾  {2} 字符列表正好重复出现 2 次  - 另一个连字符  [ 字符列表的开头  [:digit:] POSIX 数字类  ] 字符列表的结尾  {4} 字符列表正好重复出现 4 次  | 替换元字符；结束第一个选项并开始下一个替换表达式 [ 字符列表的开头  [:digit:] POSIX 数字类 ] 字符列表的结尾 {9} 字符列表正好重复出现 9 次  ) 结束圆括号，结束用于替换的子表达式组 $ 定位元字符，指示行尾；没有额外的字符能够符合模式

https://www.douban.com/note/31849478/

http://blog.itpub.net/13994314/viewspace-1006120/