Oracle 判断某個字段的值是不是数字

摘要: 壹共有三种方法，分别是使用 to_number()、regexp_like() 和 translate() 三种函数来进行匹配。如果字段的值是数字，返回1，否则返回0。由于 Oracle 数据库本身没有提供检测字段是否为数字的方法，因此我们需要自行创建壹個 isnumeric() 的函数，然后再调用它来进行判断。

壹共有三种方法，分别是使用 to_number()、regexp_like() 和 translate() 三种函数来进行匹配。如果字段的值是数字，返回1，否则返回0，具体的实现如下，三种方法任选壹种即可。运行结果：如果字符串是数字格式则返回1，不是则返回0。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64

<code class="hljs powershell">--<span class="hljs-number">1、利用 to_number CREATE OR REPLACE <span class="hljs-keyword">FUNCTION isnumeric(str <span class="hljs-keyword">IN VARCHAR2)     <span class="hljs-keyword">RETURN NUMBER IS     v_str FLOAT; <span class="hljs-keyword">BEGIN     <span class="hljs-keyword">IF str IS NULL     THEN        <span class="hljs-keyword">RETURN <span class="hljs-number">0;     <span class="hljs-keyword">ELSE        <span class="hljs-keyword">BEGIN           SELECT TO_NUMBER (str)             INTO v_str             FROM DUAL;        EXCEPTION           WHEN INVALID_NUMBER           THEN              <span class="hljs-keyword">RETURN <span class="hljs-number">0;        <span class="hljs-keyword">END;        <span class="hljs-keyword">RETURN <span class="hljs-number">1;     <span class="hljs-keyword">END <span class="hljs-keyword">IF; <span class="hljs-keyword">END isnumeric; /   --<span class="hljs-number">2、利用 regexp_like CREATE OR REPLACE <span class="hljs-keyword">FUNCTION isnumeric (str <span class="hljs-keyword">IN VARCHAR2)     <span class="hljs-keyword">RETURN NUMBER IS <span class="hljs-keyword">BEGIN     <span class="hljs-keyword">IF str IS NULL     THEN        <span class="hljs-keyword">RETURN <span class="hljs-number">0;     <span class="hljs-keyword">ELSE        <span class="hljs-keyword">IF regexp_like (str, <span class="hljs-string">'^(-{0,1}+{0,1})[0-9]+(.{0,1}[0-9]+)$')        THEN           <span class="hljs-keyword">RETURN <span class="hljs-number">1;        <span class="hljs-keyword">ELSE           <span class="hljs-keyword">RETURN <span class="hljs-number">0;        <span class="hljs-keyword">END <span class="hljs-keyword">IF;     <span class="hljs-keyword">END <span class="hljs-keyword">IF; <span class="hljs-keyword">END isnumeric; /   --<span class="hljs-number">3、利用 translate CREATE OR REPLACE <span class="hljs-keyword">FUNCTION isnumeric (str <span class="hljs-keyword">IN VARCHAR2)     <span class="hljs-keyword">RETURN NUMBER IS     v_str VARCHAR2 (<span class="hljs-number">1000); <span class="hljs-keyword">BEGIN     <span class="hljs-keyword">IF str IS NULL     THEN        <span class="hljs-keyword">RETURN <span class="hljs-number">0;     <span class="hljs-keyword">ELSE        v_str := translate(str, <span class="hljs-string">'.0123456789', <span class="hljs-string">'.');          <span class="hljs-keyword">IF v_str = <span class="hljs-string">'.' OR v_str = <span class="hljs-string">'+.' OR v_str = <span class="hljs-string">'-.' OR v_str IS NULL        THEN           <span class="hljs-keyword">RETURN <span class="hljs-number">1;        <span class="hljs-keyword">ELSE           <span class="hljs-keyword">RETURN <span class="hljs-number">0;        <span class="hljs-keyword">END <span class="hljs-keyword">IF;     <span class="hljs-keyword">END <span class="hljs-keyword">IF; <span class="hljs-keyword">END isnumeric; /</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

由于 Oracle 数据库本身没有提供检测字段是否为数字的方法，因此我们需要自己创建壹個 isnumeric() 的函数，然后再调用它来进行判断。具体的调用方式如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

<code class="hljs cs">SQL> <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">select<span class="hljs-function"> <span class="hljs-title"><span class="hljs-function"><span class="hljs-title">isnumeric<span class="hljs-function">(<span class="hljs-params"><span class="hljs-string"><span class="hljs-function"><span class="hljs-params"><span class="hljs-string">'123a'<span class="hljs-function">) <span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">from<span class="hljs-function"> dual; ISNUMERIC(<span class="hljs-string">'123A') -----------------                 <span class="hljs-number">0   SQL> <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">select<span class="hljs-function"> <span class="hljs-title"><span class="hljs-function"><span class="hljs-title">isnumeric<span class="hljs-function">(<span class="hljs-params"><span class="hljs-string"><span class="hljs-function"><span class="hljs-params"><span class="hljs-string">'123.509'<span class="hljs-function">) <span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">from<span class="hljs-function"> dual; ISNUMERIC(<span class="hljs-string">'123.509') --------------------                    <span class="hljs-number">1   SQL> <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">select<span class="hljs-function"> <span class="hljs-title"><span class="hljs-function"><span class="hljs-title">isnumeric<span class="hljs-function">(<span class="hljs-params"><span class="hljs-string"><span class="hljs-function"><span class="hljs-params"><span class="hljs-string">'123.205.10.8'<span class="hljs-function">) <span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">from<span class="hljs-function"> dual; ISNUMERIC(<span class="hljs-string">'123.205.10.8') -------------------------                         <span class="hljs-number">0</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>