How to determine whether a string is all numeric in Oracle

How to determine whether a string is all numeric in Oracle

Studied: http://www.cnblogs.com/zrcoffee/archive/2012/12/11/2812744.html

This article describes 4 ways to determine whether a string is all digital, and also introduces a small trick of translate function to extract a number from any string (call 2 translate function). This method is found by a colleague of the company, it is very convenient to use, but it is a little difficult to understand.
1, the ASCII code to determine whether the number, between [48, 57], (ASCII (' 0 ') =, ASCII (' 9 ') = ' 57 ')
2. Call the cast function to attempt to cast to numeric or number, not a valid string of numbers that throws an exception
3, call the Translate function, remove all [0-9] numbers, see if it is an empty string
4, call the regular expression, pattern matching (10g version of the newly added function)

--The ASCII code determines whether the number, between [48, 57], (ASCII (' 0 ') =, ASCII (' 9 ') = ' "DECLARE    Str             VARCHAR2(Ten) := '123a'; Val NUMERIC (Ten); Iint; Kint; Flag BOOLEAN;BEGINflag:=TRUE;  forIinch 1..TenLoop--whether the new password is 6 digitsK:= ASCII(Substr (StrI1)); ifK<  - orK>  $  Thenflag:=FALSE; End if; EndLOOP; IFFlag=True ThenDbms_output.put_line (Str || 'is a sequence of numbers [0-9]'); ELSEDbms_output.put_line (Str || 'not a sequence of numbers [0-9]'); END IF;END;--call the cast function to attempt to cast to numeric or number, not a valid string of numbers that throws an exceptionDECLARE    Str    VARCHAR2(Ten) := '123'; Val NUMERIC (Ten);BEGINVal:= CAST(Str  asNUMERIC); Dbms_output.put_line (Str || 'is a sequence of numbers [0-9]'); EXCEPTION whenValue_error Then --string to real error       --dbms_output.put_line (SQLCODE | | ', ' | | SQLERRM);Dbms_output.put_line (Str || 'not a sequence of numbers [0-9]');END;--call the Translate function to remove all [0-9] digits to see if the string is emptyDECLARE    Str VARCHAR2(Ten) := '123ABC';BEGIN    IF Replace(Translate (Str,'0123456789','0'),'0',"') is NULL  ThenDbms_output.put_line (Str || 'is a sequence of numbers [0-9]'); ELSEDbms_output.put_line (Str || 'not a sequence of numbers [0-9]'); END IF;END;--call regular expressions for pattern matching (new features added with 10g version)SELECT * fromDualWHERERegexp_like ('1234','^[[:d igit:]]+$');--extracts a number string from any string (calls 2 times the translate function). --assume that the initial string is str. First, all the numbers in Str are replaced with spaces, and the output is recorded as str2;--second, for each of the characters in the STR2 string that appears in Str, if the first word of str2 is replaced with a space, the other characters are all removed .DECLARE    --str VARCHAR2 (100): = ' Passport 01 Zhejiang 2 3 Kunshan 4 su 3 ';    Str    VARCHAR2( -) := 'passport Zhejiang kunshan su 4'; RETVARCHAR2(Ten);BEGINret:=TRIM (TRANSLATE (Str, Trim (TRANSLATE (Str,'1234567890','          ')),' ')); Dbms_output.put_line (ret);END;

How to determine whether a string is all numeric in Oracle