如何判断一个字符串的内容是否是数值

     来源：赛迪网    作者：Arthur

问题:判断一个字符串的内容是否是数值。

解决方法：利用Oracle数据库自带的TO_NUMBER函数（增加了异常处理部分，防止非数字类型导致函数异常而中断执行）。

SQL> CREATE OR REPLACE FUNCTION F_IS_NUM(P_NUM IN VARCHAR2) RETURN VARCHAR2 AS

2 V_TMP NUMBER;

3 BEGIN

4 IF P_NUM IS NULL THEN

5 RETURN NULL;

6 END IF;

7 V_TMP := TO_NUMBER(P_NUM);

8 RETURN 'T';

9 EXCEPTION

10 WHEN OTHERS THEN

11 RETURN 'N';

12 END;

13 /

函数已创建。

SQL> CREATE OR REPLACE FUNCTION F_IS_NUM1(P_NUM IN VARCHAR2) RETURN VARCHAR2 AS

2 V_NUM_DOT NUMBER DEFAULT 0;

3 BEGIN

4 IF P_NUM IS NULL THEN

5 RETURN NULL;

6 END IF;

7 FOR I IN 1..LENGTH(P_NUM) LOOP

8 CASE SUBSTR(P_NUM, I, 1)

9 WHEN '0' THEN NULL;

10 WHEN '1' THEN NULL;

11 WHEN '2' THEN NULL;

12 WHEN '3' THEN NULL;

13 WHEN '4' THEN NULL;

14 WHEN '5' THEN NULL;

15 WHEN '6' THEN NULL;

16 WHEN '7' THEN NULL;

17 WHEN '8' THEN NULL;

18 WHEN '9' THEN NULL;

19 WHEN '.' THEN

20 V_NUM_DOT := V_NUM_DOT + 1;

21 IF V_NUM_DOT > 1 THEN

22 RETURN 'N';

23 END IF;

24 WHEN '-' THEN

25 IF I != 1 THEN

26 RETURN 'N';

27 END IF;

28 WHEN '+' THEN

29 IF I != 1 THEN

30 RETURN 'N';

31 END IF;

32 ELSE RETURN 'N';

33 END CASE;

34 END LOOP;

35 RETURN 'T';

36 END;

37 /

函数已创建。

SQL> CREATE TABLE T (NUM_STR VARCHAR2(100));

表已创建。

SQL> INSERT INTO T VALUES ('-5');

已创建 1 行。

SQL> INSERT INTO T VALUES ('2.2342');

已创建 1 行。

SQL> INSERT INTO T VALUES ('+123.1234');

已创建 1 行。

SQL> INSERT INTO T VALUES ('-5-34');

已创建 1 行。

SQL> INSERT INTO T VALUES ('1230234J342');

已创建 1 行。

SQL> INSERT INTO T VALUES ('5.524.2');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL>

SQL> COL NUM_STR FORMAT A12

SQL> COL IS_NUM FORMAT A6

SQL> SELECT NUM_STR, F_IS_NUM(NUM_STR) IS_NUM FROM T;

NUM_STR IS_NUM

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

-5 T

2.2342 T

+123.1234 T

-5-34 N

1230234J342 N

5.524.2 N

已选择6行。

SQL> SELECT NUM_STR, F_IS_NUM1(NUM_STR) IS_NUM FROM T;

NUM_STR IS_NUM

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

-5 T

2.2342 T

+123.1234 T

-5-34 N

1230234J342 N

5.524.2 N

已选择6行。

采用第二种方法在处理较大数据量且其中大部分为非数字类型的数据时，效率较高。

SQL> SET AUTOT TRACE STAT

SQL> SET TIMING ON

SQL> SELECT F_IS_NUM(OBJECT_ID) FROM DBA_OBJECTS;

已选择6291行。

已用时间: 00: 00: 00.04

Statistics

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

7 recursive calls

0 db block gets

4809 consistent gets

0 physical reads

0 redo size

82590 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

SQL> SELECT F_IS_NUM(OBJECT_ID) FROM DBA_OBJECTS;

已选择6291行。

已用时间: 00: 00: 00.04

Statistics

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

0 recursive calls

0 db block gets

4807 consistent gets

0 physical reads

0 redo size

82590 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

SQL> SELECT F_IS_NUM1(OBJECT_ID) FROM DBA_OBJECTS;

已选择6291行。

已用时间: 00: 00: 00.04

Statistics

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

7 recursive calls

0 db block gets

4809 consistent gets

0 physical reads

0 redo size

82591 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

SQL> SELECT F_IS_NUM1(OBJECT_ID) FROM DBA_OBJECTS;

已选择6291行。

已用时间: 00: 00: 00.04

Statistics

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

0 recursive calls

0 db block gets

4807 consistent gets

0 physical reads

0 redo size

82591 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

假如输入的参数以数字类型为主，则两者效率差不多。

假如输入的参数大部分无法转化为数字类型，则第二种方法的效率会更高。

SQL> SELECT F_IS_NUM(OBJECT_NAME) FROM DBA_OBJECTS;

已选择6291行。

已用时间: 00: 00: 00.08

Statistics

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

7 recursive calls

0 db block gets

4809 consistent gets

0 physical reads

0 redo size

82591 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

SQL> SELECT F_IS_NUM(OBJECT_NAME) FROM DBA_OBJECTS;

已选择6291行。

已用时间: 00: 00: 00.07

Statistics

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

0 recursive calls

0 db block gets

4807 consistent gets

0 physical reads

0 redo size

82591 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

SQL> SELECT F_IS_NUM1(OBJECT_NAME) FROM DBA_OBJECTS;

已选择6291行。

已用时间: 00: 00: 00.04

Statistics

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

7 recursive calls

0 db block gets

4809 consistent gets

0 physical reads

0 redo size

82592 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

SQL> SELECT F_IS_NUM1(OBJECT_NAME) FROM DBA_OBJECTS;

已选择6291行。

已用时间: 00: 00: 00.04

Statistics

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

0 recursive calls

0 db block gets

4807 consistent gets

0 physical reads

0 redo size

82592 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

假如不采用TO_NUMBER而是使用对字符串中每个字符依次判断的方法，则会增加复杂的程度。