-
Numeric field
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
Last edited by hrishy; 02-24-2004 at 06:48 AM.
-
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...
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>
Looks like ASCII has it so far. Any other suggestions?
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
-
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'
Since quite a lot was said about performance, I woulkd further simplify the above suggestion to:
Code:
v_str BETWEEN '0' AND '9'
ommiting unneccesary SUBSTR() call.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
-
Originally posted by padders
LOL. Good call.
Actually not so good. I just realised I've made a stupid mistake with
Code:
v_str BETWEEN '0' AND '9'
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 >= '0' AND v_str < ':'
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 using
SUBSTR (v_str, 1, 1) BETWEEN '0' AND '9'
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|