Hi
Is there any function to check that a value starts with numeric only? Thanks!
Printable View
Hi
Is there any function to check that a value starts with numeric only? Thanks!
Hi
You can try the to_number
SQL> select to_number('A') from dual;
select to_number('A') from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> ed
Wrote file afiedt.buf
1* select to_number('10') from dual
SQL> /
TO_NUMBER('10')
---------------
10 function
1 declare
2 l_number number;
3 begin
4 select to_number('A') into l_number from dual;
5 exception
6 when invalid_number then
7 dbms_output.put_line('Invalid Number');
8* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> /
Invalid Number
PL/SQL procedure successfully completed.
regards
Hrishy
Try:
translate(substr(my_col,1,1),'1234567890','0000000000') = '0'
create your own "is_number" function
create or replace function is_number (p_value in varchar2)
return boolean as
l_number number;
begin
l_number := p_value;
return true;
exception
when others then
return false;
end is_number;
Several approaches spring to mind...
Looks like ASCII has it so far. Any other suggestions?Code:Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 v_cnt PLS_INTEGER := 10 ** 5;
3 v_tme PLS_INTEGER := 0;
4 v_bln BOOLEAN;
5 v_str VARCHAR2 (10) := '9ASDJADJ';
6 BEGIN
7 v_tme := DBMS_UTILITY.get_time;
8
9 FOR i IN 1 .. v_cnt
10 LOOP
11 v_bln := ASCII (v_str) BETWEEN 48 AND 57;
12 END LOOP;
13
14 DBMS_OUTPUT.put_line ('[ASCII (v_str) BETWEEN 48 AND 57] ' ||
15 (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
16
17 v_tme := DBMS_UTILITY.get_time;
18
19 FOR i IN 1 .. v_cnt
20 LOOP
21 v_bln := LPAD (v_str, 1) BETWEEN '0' AND '9';
22 END LOOP;
23
24 DBMS_OUTPUT.put_line ('[LPAD (v_str, 1) BETWEEN ''0'' AND ''9''] ' ||
25 (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
26
27 v_tme := DBMS_UTILITY.get_time;
28
29 FOR i IN 1 .. v_cnt
30 LOOP
31 v_bln := CHR (ASCII (v_str)) BETWEEN '0' AND '9';
32 END LOOP;
33
34 DBMS_OUTPUT.put_line ('[CHR (ASCII (v_str)) BETWEEN ''0'' AND ''9''] ' ||
35 (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
36
37 v_tme := DBMS_UTILITY.get_time;
38
39 FOR i IN 1 .. v_cnt
40 LOOP
41 v_bln := SUBSTR (v_str, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9');
42 END LOOP;
43
44 DBMS_OUTPUT.put_line ('[SUBSTR (v_str, 1) IN (''0'', ''1'', ''2'', ''3'', ''4'', ''5'', ''6', ''7'', ''8'', ''9'')] ' ||
45 (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
46
47 v_tme := DBMS_UTILITY.get_time;
48
49 FOR i IN 1 .. v_cnt
50 LOOP
51 v_bln := SUBSTR (v_str, 1) BETWEEN '0' AND '9';
52 END LOOP;
53
54 DBMS_OUTPUT.put_line ('[SUBSTR (v_str, 1) BETWEEN ''0'' AND ''9''] ' ||
55 (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
56
57 v_tme := DBMS_UTILITY.get_time;
58
59 FOR i IN 1 .. v_cnt
60 LOOP
61 v_bln := LTRIM (SUBSTR (v_str, 1, 1), '0123456789') IS NULL;
62 END LOOP;
63
64 DBMS_OUTPUT.put_line ('[LTRIM (SUBSTR (v_str, 1, 1), ''0123456789'') IS NULL] ' ||
65 (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
66
67 v_tme := DBMS_UTILITY.get_time;
68
69 FOR i IN 1 .. v_cnt
70 LOOP
71 v_bln := LENGTH (LTRIM (v_str, '0123456789')) < LENGTH (v_str);
72 END LOOP;
73
74 DBMS_OUTPUT.put_line ('[LENGTH (LTRIM (v_str, ''0123456789'')) < LENGTH (v_str)] ' ||
75 (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
76
77 v_tme := DBMS_UTILITY.get_time;
78
79 FOR i IN 1 .. v_cnt
80 LOOP
81 v_bln := TRANSLATE (SUBSTR (v_str, 1, 1), '1234567890', '0000000000') = '0';
82 END LOOP;
83
84 DBMS_OUTPUT.put_line ('[TRANSLATE (SUBSTR (v_str, 1, 1), ''1234567890'', ''0000000000'') =
'0''] ' ||
85 (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
86 END;
87 /
[ASCII (v_str) BETWEEN 48 AND 57] 26 hsecs.
[LPAD (v_str, 1) BETWEEN '0' AND '9'] 43 hsecs.
[CHR (ASCII (v_str)) BETWEEN '0' AND '9'] 44 hsecs.
[SUBSTR (v_str, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')] 92 hsecs.
[SUBSTR (v_str, 1) BETWEEN '0' AND '9'] 42 hsecs.
[LTRIM (SUBSTR (v_str, 1, 1), '0123456789') IS NULL] 54 hsecs.
[LENGTH (LTRIM (v_str, '0123456789')) < LENGTH (v_str)] 45 hsecs.
[TRANSLATE (SUBSTR (v_str, 1, 1), '1234567890', '0000000000') = '0'] 303 hsecs.
PL/SQL procedure successfully completed.
SQL>
Padders
Watch out!
substr('12345',1) = '12345' and ascii() is platform dependant.
I'd vote for:
SUBSTR (v_str, 1, 1) BETWEEN '0' AND '9'
You're right about that Dapi. Actually I notice several mistakes in my script. So much for a quick test eh?
I tend to agree with your conclusion - it is not only quick but readable / intuitive etc.
Padders
Since quite a lot was said about performance, I woulkd further simplify the above suggestion to:Quote:
Originally posted by DaPi
Watch out!
substr('12345',1) = '12345' and ascii() is platform dependant.
I'd vote for:
SUBSTR (v_str, 1, 1) BETWEEN '0' AND '9'
ommiting unneccesary SUBSTR() call.Code:v_str BETWEEN '0' AND '9'
LOL. Good call.
Padders
Actually not so good. I just realised I've made a stupid mistake withQuote:
Originally posted by padders
LOL. Good call.
since this will be false for any string beginning with '9' and followed by anything else. Since this is not what the original poster was looking for and since the character following "9" in any ASCII-based characterset is ":", my above suggestion should be rewritten into:Code:v_str BETWEEN '0' AND '9'
But then again, after reading this code only the most geekish of all the geeks will know what this code is all about, so I'd step back and vote for DaPi's suggestion (never mind the minor performance penalty) of usingCode:v_str >= '0' AND v_str < ':'
SUBSTR (v_str, 1, 1) BETWEEN '0' AND '9'
Hmmm - platform dependant again. Our EBCDIC colleagues will be up in arms :)Quote:
. . . and since the character following "9" in any ASCII-based characterset is ":"
Yes, true. That's why I explicitly narrowed it to "... in any ASCII-based characterset ...".
Geek, moi?Quote:
But then again, after reading this code only the most geekish of all the geeks will know what this code is all about,