### Is this string a number? Really?

Note that I have slightly complicated Jared's approach to allow for fake_number values of varying length:

So far so good. Let's add another record....

SQL> select * from detect_numeric

2 order by 1

3 /

FAKE_NUMBER

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

000000

000001

000002

000010

000011

000012

000020

000021

000022

9 rows selected.

SQL> select fake_number

2 from detect_numeric

3 where lpad('|', length(fake_number), '|')

4 = translate(fake_number,'0123456789','||||||||||')

5 order by 1

6 /

FAKE_N

------

000000

000001

000002

000010

000011

000012

000020

000021

000022

9 rows selected.

SQL>

Wha'pen? Isn't 123.45 is a number? Well, no, not in this context. The TRANSLATE() call is only counting digits. Hmmm, obviously we need to allow for decimal points.

SQL> insert into detect_numeric values ('123.45')

2 /

1 row created.

SQL> select fake_number

2 from detect_numeric

3 where lpad('|', length(fake_number), '|')

4 = translate(fake_number,'0123456789','||||||||||')

5 order by 1

6 /

FAKE_N

------

000000

000001

000002

000010

000011

000012

000020

000021

000022

9 rows selected.

SQL>

Problem solved? Not quite. There's more to being numeric than just comprising digits and decimal points. They have to be in the right quantities and right places. Let's add an IP address to the mix....

SQL> select fake_number

2 from detect_numeric

3 where lpad('|', length(fake_number), '|')

4 = translate(fake_number,'0123456789.','|||||||||||')

5 order by 1

6 /

FAKE_N

------

000000

000001

000002

000010

000011

000012

000020

000021

000022

123.45

10 rows selected.

SQL>

And that's why we might need a function like IS_NUMERIC(), which wraps a TO_NUMBER call:

SQL> insert into detect_numeric values ('127.0.0.1')

2 /

1 row created.

SQL> select fake_number

2 from detect_numeric

3 where lpad('|', length(fake_number), '|')

4 = translate(fake_number,'0123456789.','|||||||||||')

5 order by 1

6 /

FAKE_NUMBER

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

000000

000001

000002

000010

000011

000012

000020

000021

000022

123.45

127.0.0.1

11 rows selected.

SQL>

SQL> create or replace function is_numeric

2 (p_str in varchar2, p_fmt_msk in varchar2 := null)

3 return varchar2

4 as

5 return_value varchar2(5);

6 n number;

7 begin

8 begin

9 if p_fmt_msk is null then

10 n := to_number(p_str);

11 else

12 n := to_number(p_str, p_fmt_msk);

13 end if;

14 return_value := 'TRUE';

15 exception

16 when others then

17 return_value := 'FALSE';

18 end;

19 return return_value;

20 end;

21 /

Function created.

SQL> column is_numeric format a10

SQL> select fake_number, is_numeric(fake_number) is_numeric

2 from detect_numeric

3 order by 2,1

4 /

FAKE_NUMBER IS_NUMERIC

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

127.0.0.1 FALSE

000000 TRUE

000001 TRUE

000002 TRUE

000010 TRUE

000011 TRUE

000012 TRUE

000020 TRUE

000021 TRUE

000022 TRUE

123.45 TRUE

11 rows selected.

SQL>

All of which underlines the importance of understanding the data with which we are working. If we just need to assert that a string consists solely of digits then a simple TRANSLATE() will suffice and will be very efficient. But if we need to assert something more precise - that the string contains a valid number - then we may require a slower but more reliable approach.

Incidentally, anyone who is interested in seeing how to use 10g's regex functionality to winnow numeric strings from non-numeric strings should read this OTN Forum post from CD.

## 0 Comments:

Post a comment

<< Home