Skip to Main Content
  • Questions
  • Validation of a number within a VARCHAR2 field

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Question and Answer

Tom Kyte

Thanks for the question, Alan.

Asked: January 14, 2002 - 9:13 am UTC

Last updated: April 01, 2008 - 9:11 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,
Could you tell me how to quickly determine whether values in a VARCHAR2 field are actually numbers? I would like to do this using a single SQL statement, rather than a explicit cursor in a block.
The problem is I'm working on a system that's already been designed and would like to speed up the validation of the data within a particular table, which holds both numbers and characters in a certain column.
For example, a much simplified look at my table is:

TMP
TMPID NUMBER,
TMPVALUE VARCHAR2(30) (Contains either text or number data)
TMPTYPE VARCHAR2(1) (N = number, T = text)

It is possible for the TMPVALUE field to hold text even when the TMPTYPE field is 'N' (due to lack of access to the data-entry part of the system, I can't change this area!).

So, I need to find out which records contain text where TMPTYPE = 'N'. There can be hundreds of thousands of entries, and I'd like to avoid using an index-based function. I tried putting a function into the where clause, but this obviously slows the query down big time.

Thanks for the help (I've got a feeling I'm being really stupid about this one!)



and Tom said...

Ok, we can start by knowing that any string that has something other then 0123456789. cannot be a number and things with those MIGHT be a number (eg: 8.1.7 is not a number, its a version).

We can use a predicate like this:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( tmpvalue varchar2(10), tmptype varchar2(1) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( '123.54', 'N' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( '123.54a', 'N' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( '123.54.5', 'N' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function my_to_number( p_num in varchar2 ) return number
2 as
3 x number;
4 num_val_error exception;
5 pragma exception_init( num_val_error, -6502 );
6 begin
7 x := to_number( p_num );
8 return 1;
9 exception
10 when num_val_error then return 0;
11 end;
12 /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
2 from t
3 where tmptype = 'N'
4 and decode( replace(translate(tmpvalue,'1234567890.','00000000000'),'0',''),
5 NULL, my_to_number(tmpvalue),
6 0 ) = 0;

TMPVALUE T
---------- -
123.54a N
123.54.5 N


it'll only call the PLSQL function, my_to_number, when we have something that is all digits and dots. The replace/translate will more efficiently find the text strings. If you are really daring -- you can assume that anything with digits and dots is a number and skip the my_to_number test...





Rating

  (57 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

another solution?

Barbara Boehmer, January 14, 2002 - 6:45 pm UTC

What about negative numbers? And, how about this?:

create or replace function numbers_only
(p_num in varchar2)
return number deterministic
as
x number;
begin
x := to_number (p_num);
return x;
exception
when others then return null;
end numbers_only;
/
select *
from t
where tmptype = 'N'
and numbers_only (tmpvalue) is null
/


Tom Kyte
January 14, 2002 - 8:25 pm UTC

sure, add a dash into the replace function and turn the dash into a zero. would hope that the example would lead to a real implementation...

Your approach would work but would

a) call the plsql function for every row -- I wanted to avoid that when possible, hence the decode trick (its faster to not call the plsql when possible)

b) record tmpvalue values that are NULL as not being a number -- but they are a number. Adding the is not null as you do below corrects that.


additional

Barbara Boehmer, January 14, 2002 - 6:53 pm UTC

It just dawned on me that there might be null values in the tmpvalue column, so if you are looking for text strings, not just non-numbers, revise that to:

select count(*)
from t
where tmptype = 'N'
and numbers_only (tmpvalue) is null
and tmpvalue is not null
/


issues

Barbara Boehmer, January 14, 2002 - 7:07 pm UTC

I see two issues:

1. Unless we use a function to evaluate every value, are we really sure whether it is or is not a number? In addition to negative numbers, what about things like 1+2. Is that legitimate? Should we count that as 3? Maybe it depends on the data?

2. Which is faster, one deterministic function, or decode and replace and translate and sometimes a function, and how do we tell? Could that, once again, depend on the data?



Tom Kyte
January 14, 2002 - 8:28 pm UTC

1) the decode trick shorts circuts us from having to call PLSQL when the string is DEFINITELY not a number -- we call the function for everything that MIGHT be a number. We would not count 1+2 as a number (we could using

execute immediate 'select ' || p_str || ' from dual' into l_number

instead of just assigning the string to a number but that would be even slower...

2) deterministic doesn't even come into play here, it is not relevant. it affects nothing.

The decode( replace(translate())....., my_to_number() ) will be faster if it can short circut having to call my_to_number() a couple of time (decode, replace, translate are pretty fast). It will depend on the data, but since we know some of the data is bad-- we'll short circut a couple of times so it probably is worth the effort.

deterministic

Barbara Boehmer, January 14, 2002 - 10:42 pm UTC

Tom,

You say, "deterministic doesn't even come into play here, it is not relevant. it affects nothing."?

My Oracle Advanced PL/SQL Student Guide page 2-28 (kit produced 2/23/01 BOM Updated 7/20/00) says, "The hint DETERMINISTIC helps the optimizer avoid redundant function calls." Further up on the page, it says, "Means the SQL may choose to use a cached copy of the return result rather than actually invoking the function."

So,if there are many of the same value, wouldn't the function potentially only be invoked once per value, using cached results for repeat values, thereby running faster?

I jumped in on this one because several of us have been bouncing around various ideas about validation of numbers, dates, and so forth, with and without functions, with and without deterministic, on the OTN forums, the PL/SQL pipelines, and Orafans. I am hoping to get detailed clarification, so that, in the future, I can just provide a link to this as a final reference. I think most of us, at least on the OTN forums, regard you as the ultimate authority.

Also, I know you said that the combination of decode and replace and translate with an occasional function call is faster than multiple function calls, but will you please provide some more detail as to why or better yet some proof? I tried SET AUTOTRACE ON and running your function and mine and saw no difference, but it was on a very small sample of data. I didn't try TKPROF.

Thanks,
Barbara


Tom Kyte
April 01, 2008 - 2:32 pm UTC

There is NO way the optimizer will be able to avoid redundant calls here as there are no redudant calls to be removed.  

It would not reuse the values for called plsql within the same result set.  

So, here is the complete example:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( tmpvalue varchar2(10), tmptype varchar2(1) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select 1234567890, 'N' from all_objects;

17130 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select 'abcdefghij', 'N' from all_objects;

17130 rows created.

<b>so, I've made half the rows numbers, half the rows not numbers...</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package my_state
  2  as
  3     cnt number;
  4  end;
  5  /

Package created.

<b>that'll be so we can count how many times our function was invoked...</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function non_deterministic( p_num in varchar2 ) return number
  2  as
  3     x number;
        num_val_error exception;
        pragma exception_init( num_val_error, -6502 );
  4  begin
  5      my_state.cnt := my_state.cnt+1;
  6     x := to_number( p_num );
  7     return 1;
  8  exception
  9     when num_val_error then return 0;
 10  end;
 11  /
Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function is_deterministic( p_num in varchar2 ) return number
  2  DETERMINISTIC
  3  as
  4     x number;
        num_val_error exception;
        pragma exception_init( num_val_error, -6502 );

  5  begin
  6      my_state.cnt := my_state.cnt+1;
  7     x := to_number( p_num );
  8     return 1;
  9  exception
 10     when num_val_error then return 0;
 11  end;
 12  /
Function created.

<b>and those are the two functions.... Now, we'll test the decode vs no decode and deterministic vs non deterministic</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.

<b>using CBO -- just in case this deterministic option would be a function of it</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sql_trace=true;
Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec my_state.cnt := 0
PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*)
  2    from t
  3   where tmptype = 'N'
  4     and decode( replace(translate(tmpvalue,'+-1234567890.','0000000000000'),'0',''),
  5                 NULL, non_deterministic(tmpvalue), 0 ) = 0
  6  /

  COUNT(*)
----------
     17130

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( my_state.cnt );
17130

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec my_state.cnt := 0
PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*)
  2    from t
  3   where tmptype = 'N'
  4     and decode( replace(translate(tmpvalue,'+-1234567890.','0000000000000'),'0',''),
  5                 NULL, is_deterministic(tmpvalue), 0 ) = 0
  6  /

  COUNT(*)
----------
     17130

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( my_state.cnt );
17130

PL/SQL procedure successfully completed.

<b>so, that shows that both types of functions were called 17,130 times -- there is no short circut there.
</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec my_state.cnt := 0
PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*)
  2    from t
  3   where tmptype = 'N'
  4     and non_deterministic(tmpvalue) = 0
  5  /

  COUNT(*)
----------
     17130

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( my_state.cnt );
34260

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec my_state.cnt := 0

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*)
  2    from t
  3   where tmptype = 'N'
  4     and is_deterministic(tmpvalue) = 0
  5  /

  COUNT(*)
----------
     17130

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( my_state.cnt );
34260

PL/SQL procedure successfully completed.

<b>and again -- both were called the same number of times...  Now for the tkprof</b>

********************************************************************************

select count(*)
  from t
 where tmptype = 'N'
   and decode( replace(translate(tmpvalue,'+-1234567890.','0000000000000'),'0',''),
               NULL, non_deterministic(tmpvalue), 0 ) = 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.26       1.26          0         89          5           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.26       1.26          0         89          5           1

********************************************************************************

select count(*)
  from t
 where tmptype = 'N'
   and decode( replace(translate(tmpvalue,'+-1234567890.','0000000000000'),'0',''),
               NULL, is_deterministic(tmpvalue), 0 ) = 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.29       1.29          0         89          5           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.30       1.29          0         89          5           1

********************************************************************************

select count(*)
  from t
 where tmptype = 'N'
   and non_deterministic(tmpvalue) = 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.16       5.16          0         89          5           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.16       5.17          0         89          5           1

********************************************************************************

select count(*)
  from t
 where tmptype = 'N'
   and is_deterministic(tmpvalue) = 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.13       5.13          0         89          5           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.13       5.13          0         89          5           1

<b>here, bypassing calling PLSQL when possible made a huge difference, huge difference.  Now, the cool thing is -- looking forward to 9i, the numbers on the same exact box were:</b>


********************************************************************************

select count(*)
  from t
 where tmptype = 'N'
   and decode( replace(translate(tmpvalue,'+-1234567890.','0000000000000'),'0',''),
               NULL, non_deterministic(tmpvalue), 0 ) = 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.08          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.71       0.72          0         96          3           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.71       0.80          0         96          3           1

********************************************************************************

select count(*)
  from t
 where tmptype = 'N'
   and decode( replace(translate(tmpvalue,'+-1234567890.','0000000000000'),'0',''),
               NULL, is_deterministic(tmpvalue), 0 ) = 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.73       0.73          0         96          3           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.74       0.73          0         96          3           1

********************************************************************************

select count(*)
  from t
 where tmptype = 'N'
   and non_deterministic(tmpvalue) = 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.03       1.04          0         96          3           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.05       1.08          0         96          3           1

********************************************************************************

select count(*)
  from t
 where tmptype = 'N'
   and is_deterministic(tmpvalue) = 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.01       1.00          0         96          3           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.02       1.01          0         96          3           1

<b>so, there is still a marked improvement by short circuting the call to PLSQL but it is not nearly as much AND in fact, the query that doesn't short circut in 9i actually runs faster then the 8i query showing the speed up in 9i related to calling PLSQL from SQL (it is much reduced in 9i)</b>


Deterministic is mostly useful with function based indexes.... 

additional validation scenario

Barbara Boehmer, January 14, 2002 - 11:22 pm UTC

Tom,

It occurred to me that which method is preferred might depend on the scenario and how you want to handle it. In the original post above, Alan apparently wants to identify the invalid data, the character strings in the supposed numeric field. I don't know if he then has plans for some data correction or what.

I have a slightly different approach when transferring data from old systems where the data wasn't validated to new ones where it is validated. If the data in the old table is invalid, I typically just want a null value inserted into the corresponding column in the new table and don't plan to try to clean up the old data. For example, if C is found in an age column that should be numeric, I don't plan to try to figure out what they meant to input when they typed C. So, I typically use something like (simplified for demonstration purposes):

insert into new_table (number_column)
select numbers_only (varchar2_column)
from old_table;

Would this be the best approach under these circumstances, or would you recommend something else?:

Thanks again,
Barbara


Tom Kyte
January 15, 2002 - 8:57 am UTC

sure -- that works and works cleanly. You can use the decode/replace/translate trick to filter even faster as shown above.

Nice trick

Alan, January 15, 2002 - 5:00 am UTC

An additional problem I have is that the numbers aren't necessarily using the same decimal format. For example some are entered using british nls settings and others german. This results in a mixture of number types such as 1,2399.34, 12399.34, or 12399,34. So I think I'll definitately need a user function in there somewhere (or do you think extra replace would do the trick).
My only goal is to identify which rows do not contain valid numbers, so I can flag them.

Tom Kyte
January 15, 2002 - 9:31 am UTC

you would add +- and , to the list in the replace (add three 0's as well) and then code my_to_number to try various formats when converting the number....

Excellent!

Barbara Boehmer, January 15, 2002 - 7:24 pm UTC

Tom,

Thank you very much for providing the explanation and proof in the form of a detailed example including TKPROF. It was exactly what I was looking for. Anytime this subject comes up, I can now just post a response using your method with decode, replace, translate, (including + and - just in case) and your function (without deterministic) and provide a link to this for the details.

Thanks,
Barbara


Great

Alan, January 16, 2002 - 5:36 am UTC

Thanks for the help Tom. Fantastic as always.

Optimizer And Deterministic Function

Mike, January 18, 2002 - 2:26 pm UTC

Tom,

In your example, the function is referenced only once for each row, it makes sense the optimizer doesn't do anything to it, just like you said, "there are no redudant calls to be removed". Also the function modifies the value of cnt in my_state package, it probably should be called every time it's referenced. However, in the following example, I thought the optimizer should be able to remove some redudant calls, but it didn't.

SQL> create table t as select 1 n from all_objects;
Table created.

SQL> create or replace function f1 return number
  2  as
  3     i number;
  4  begin
  5     for i in 1..100 loop
  6        null;
  7     end loop;
  8     return 1;
  9* end;
SQL> /
Function created.

SQL> l
  1  create or replace function f2 return number deterministic
  2  as
  3     i number;
  4  begin
  5     for i in 1..100 loop
  6        null;
  7     end loop;
  8     return 1;
  9* end;
SQL> /
Function created.

SQL> analyze table t compute statistics for table for all columns;
Table analyzed.

SQL> alter session set timed_statistics = true;
Session altered.

SQL> alter session set sql_trace = true;
Session altered.

SQL> select max(f1), count(f1) from t where f1 = 1;

   MAX(F1)  COUNT(F1)
---------- ----------
         1      25043

SQL> select max(f1), count(1) from t where f1 = 1;

   MAX(F1)   COUNT(1)
---------- ----------
         1      25043

SQL> select max(f2), count(f2) from t where f2 = 1;

   MAX(F2)  COUNT(F2)
---------- ----------
         1      25043

SQL> select max(f2), count(1) from t where f2 = 1;

   MAX(F2)   COUNT(1)
---------- ----------
         1      25043

SQL> select count(*) from t where f1 = 1;

  COUNT(*)
----------
     25043

SQL> select count(*) from t where f2 = 1;

  COUNT(*)
----------
     25043

Now look at the trace file:

***************************************
select max(f1), count(f1) 
from
 t where f1 = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.08       0.08          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      6.45       6.48          0         42         12           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      6.53       6.56          0         42         12           1

********************************************************************************

select max(f1), count(1) 
from
 t where f1 = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       0.07          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      3.29       3.31          0         42         12           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      3.36       3.38          0         42         12           1


********************************************************************************

select max(f2), count(f2) 
from
 t where f2 = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       0.07          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      6.52       6.55          0         42         12           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      6.59       6.62          0         42         12           1

********************************************************************************

select max(f2), count(1) 
from
 t where f2 = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       0.07          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      3.48       3.50          0         42         12           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      3.55       3.57          0         42         12           1

********************************************************************************

select count(*) 
from
 t where f1 = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       0.07          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.02          0         42         12           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.09       0.09          0         42         12           1

********************************************************************************

select count(*) 
from
 t where f2 = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       0.07          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.02          0         42         12           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.09       0.09          0         42         12           1

It looks like the deterministic key word doesn't make any difference. If I have count(f1) and max(f1) in the select statement, it's going to call function twice. It's also interesting that the optimizer only evalute f1 = 1 once for the whole table instead of once for each row even when f1 is not a deterministic function. When I run the following query twice, it gives different results.

SQL> l
  1  select * from
  2   ( select dbms_random.random r from t )
  3  where r > 0
  4* and rownum < 10
SQL> /

no rows selected

SQL> /

         R
----------
-911661048
-664769205
 150161117
-397720797
-864258392
-1.555E+09
-845515666
-1.723E+09
 765505919

9 rows selected.

Thanks,
Mike 

Tom Kyte
January 19, 2002 - 10:14 am UTC

That is sort of what I said -- it doesn't factor it out in these cases, you can HELP it to do that.

DETERMINISTIC, so far, it mostly useful with function based indexes (where it is MANDATORY).

There are cases where it will factor it out on its own in the where clause, consider:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package state_pkg
  2  as
  3          cnt number;
  4  end;
  5  /
Package created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function f return number
  2  as
  3  begin
  4          state_pkg.cnt := state_pkg.cnt+1;
  5          return 1;
  6  end;
  7  /
Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select rownum r from all_objects;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec state_pkg.cnt := 0;
PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where r = f();

         R
----------
         1

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( state_pkg.cnt );
17137

PL/SQL procedure successfully completed.

<b>so, there is called it once per row apparently, sort of as we expected by now...</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec state_pkg.cnt := 0;
PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where r = (select f() from dual);

         R
----------
         1

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( state_pkg.cnt );
1

PL/SQL procedure successfully completed.

<b>Ahh, interesting, by hiding it in a subquery, it did it once per QUERY.  But, you'll discover this doesn't always work as we'll see in a moment...</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> exec state_pkg.cnt := 0;
PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t, (select f() x from dual) where r = x;

         R          X
---------- ----------
         1          1

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( state_pkg.cnt );
17138

PL/SQL procedure successfully completed.

<b>You see, if we turned the "where r = (subquery)" into a join (can happen) we are back to once/row.  What we have to do then is involve rownum or some other thing (group by, order by) in the subquery to make us materialize the subquery and use that instead, so:</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec state_pkg.cnt := 0;
PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t, (select f() x from dual where rownum=1) where r = x;

         R          X
---------- ----------
         1          1

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( state_pkg.cnt );
1

PL/SQL procedure successfully completed.

<b>Now, we are back to one.  This all changes however, if we index R:</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(r);
Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec state_pkg.cnt := 0;
PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where r = f();

         R
----------
         1

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( state_pkg.cnt );
2

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 

<b> now the optimizer evaluated F and used that value to do an INDEX RANGE scan...  Less rows to work with.  We can see it does this only one time (it calls the function 2 times for some reason but only does that once) by updating some rows:</b>



ops$tkyte@ORA817DEV.US.ORACLE.COM> exec state_pkg.cnt := 0;

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where r = f();

         R
----------
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1

19 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( state_pkg.cnt );
2

PL/SQL procedure successfully completed.

<b>and seeing that the function was still only called 2 times...</b>


What I like to do is:

A) if the function is in fact returning a constant like yours -- USE A BIND VARIABLE or if you cannot bind -- use an application context and SYS_CONTEXT.  They are appropriate for constants.  Bind variables = BEST, sys_context = 2cnd best, plsql function = way way back in 3rd.

B) use the (subquery) with rownum trick whenever possible

C) postpone calling functions in the select list until the very last possible moment to ensure they are called as infrequently as possible, eg: instead of coding:

select f(x), .... from .....

I would:

select f(x), ....
  from ( select ROWNUM, ..... from .... )


where the inner query is simply the original query with the functions removed and rownum added (so no view merging takes place)


 

What if without REPLACE

Irina, February 04, 2002 - 3:56 am UTC

translate(l_str,'x0123456789','x') ;
translate(l_str,chr(0)||'0123456789',chr(0)) - delete all digits, replace is not needed

How is this possible

Kumar, June 19, 2002 - 5:24 pm UTC

select
((to_number(b.ARRANGE_FEE_TXT))),b.tr_id
from
tr_deal_source b,
tr_fac_currency d ,
deal_source c
where c.deal_id = b.deal_id
and b.tr_id = d.tr_id
and c.data_source_id = b.data_source_id
and c.data_source_id = 13
and c.deal_currency_id = d.currency_id
and b.ARRANGE_FEE_TXT not in ('N/A','TBD','NA')
and b.ARRANGE_FEE_TXT is not null

Why i am getting Invalid Number Error even filtering
Varchar value from the table for that column?

Only possble varchar value in that column is N/A,TBD,NA
(Note:I checked Data in the Table)

Tom Kyte
June 19, 2002 - 9:50 pm UTC

well, without the table defs themselves -- it is not possible to answer.

You assume it is ont he to_number(arrange_fee_text), but it might not be.

Re-How is this possible

Kumar, June 20, 2002 - 8:44 am UTC

I am sure the error i am getting bcos of to_number function in the select.If i remove the to_number function from the select then it retrieves without any error.

workaround :
Using Decode,replace & Translate in to_number function solves this.But my question is even after filtering the varchar value in where clause why to_number function is not working?


Tabel def:

TR_DEAL_SOURCE:
deal_id Varchar2(10)
tr_id Number(10,0)
arrange_fee_tx Varchar2(25)

DEAL_SOURCE
deal_id Varchar2(10)
data_source_id Number(2,0)
deal_currency_id Varchar2(4)



Tom Kyte
June 20, 2002 - 11:54 am UTC

you are assuming some "order of operation" -- an order of operation which is -- in sql -- explicitly not defined. Can you supply a couple (2 or 3) rows of data with which to reproduce this issue?



Response-How this is possible

Kumar, June 20, 2002 - 1:05 pm UTC

Tom-
Thanks for your quick response.
I have gone thru this link->
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1299201885045,%7Bto_number%7D <code>

It seems like somebody already reported this has bug and got back as not a BUG but to use Decode in the to_nunber function.



Tom Kyte
June 20, 2002 - 1:18 pm UTC

it isn't a bug in the link you referred me to. They were 100% dependent on the order of operation in a sql predicate and there IS NO DEFINED ORDER OF OPERATION in a sql predicate.

The only bug in that link is in the query posed by the orginal poster.

If you give me some rows, I'll explain to you what's happening. This is the old "if it is a number, store it in a number column, if it is a date, put it in a date column" problem. Give me a small test case and I'll be glad to tell you whats going on.

Now i know how is this happening

Kumar, June 21, 2002 - 1:49 pm UTC

Sorry tom.
The problem is with the data.one of the value is (1,2345).

Thanks for your suggestion!



Now i know how is this happening

Kumar, June 21, 2002 - 2:02 pm UTC

Sorry tom.
The problem is with the data.one of the value is (1,2345).

Thanks for your suggestion!



Add another Where

A reader, July 19, 2002 - 2:30 pm UTC

Tom
I agree with your solution totally.
But the following query just retrieves all the values. What if we need to query a specific tmpvalue such as
Where tmpvalue=123.4. If I plug this where clause in the following query it again gives me a number invalid error?

select * from t where tmptype = 'N'
and decode( replace(translate(tmpvalue,'1234567890.','00000000000'),'0',''),
NULL, my_to_number(tmpvalue), 0 ) = 0;


I appreciate all the help
Thanks

Tom Kyte
July 19, 2002 - 5:14 pm UTC

add where tmpvalue = '123.4' then. compare a string to a string.

order by number in varchar2 columns

Sean, November 02, 2003 - 9:24 pm UTC

create table t(c varchar2(20));

insert into t
values('101St');

insert into t
values('11CT');

insert into t
values('10 Street');

I need the result ordered by the number and ignore the char or space after the number as shown bellow:

11CT
12 Street
101St

Thanks so much for your help.

Sean

Tom Kyte
November 03, 2003 - 7:10 am UTC

ops$tkyte@ORA920> select c, to_number(substr( c, 1, length(c)-length(ltrim(translate(c,'01234567890','0000000000'),'0')))) cn
  2   from t
  3  order by to_number(substr( c, 1, length(c)-length(ltrim(translate(c,'01234567890','0000000000'),'0'))))
  4  /
 
C                            CN
-------------------- ----------
10 Street                    10
11CT                         11
101St                       101
hello world
 
ops$tkyte@ORA920>
 

Split a VARCHAR2

M S Reddy, November 25, 2003 - 4:20 pm UTC

Tom,
I have a small issue.consider this example.

TAB1
c1
-------------------------------
123 345 567 789 980 234 543 567
1234567 456789

i want to write a query and select something like this

c1
-------------------------------
123
345
567
789
980
234
543
567
1234567
456789

make one record into many records by spilting using space.
can this be done in one single SQL without looping?
my requirement is to spilt and then join that with someother table.

Thanks,
M S Reddy




Tom Kyte
November 25, 2003 - 4:34 pm UTC

it could, if there were some reasonable upper limit on the number of tokens, but it would be really obscure.


a pipeline function is what you want to investigate here.

ops$tkyte@ORA920PC> create or replace type myTable as table of varchar2(25)
  2  /
 
Type created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace function str2tbl( p_str in varchar2 ) return myTable
  2  pipelined
  3  as
  4      l_str   long default p_str || ' ';
  5      l_n        number;
  6  begin
  7      loop
  8          l_n := instr( l_str, ' ' );
  9          exit when (nvl(l_n,0) = 0);
 10          pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
 11          l_str := ltrim( substr( l_str, l_n+1 ) );
 12      end loop;
 13      return;
 14  end;
 15  /
 
Function created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create table t ( id int, str varchar2(2000) );
 
Table created.
 
ops$tkyte@ORA920PC> insert into t values ( 1, '123 345 567 789 980 234 543 567 1234567 456789');
 
1 row created.
 
ops$tkyte@ORA920PC> insert into t values ( 2, '32123 3455 5567' );
 
1 row created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select * from t;
 
        ID STR
---------- ------------------------------
         1 123 345 567 789 980 234 543 56
           7 1234567 456789
 
         2 32123 3455 5567
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select t.id,  t2.column_value
  2    from t, TABLE(str2tbl(t.str)) t2
  3  /
 
        ID COLUMN_VALUE
---------- -------------------------
         1 123
         1 345
         1 567
         1 789
         1 980
         1 234
         1 543
         1 567
         1 1234567
         1 456789
         2 32123
         2 3455
         2 5567
 
13 rows selected.
 
 

Thanks!!

M S Reddy, November 26, 2003 - 9:30 am UTC

Tom,
thanks a lot .that helped me a lot.
M S Reddy

for ver 8.1.7

Pal, January 08, 2004 - 8:50 pm UTC

Hi Tom,
My problem is similar to above but I have Ora 8.1.7
and it seems the pipeline works only for ver 9
Also I have max 5 delimited data per row as shown

TABLE1
c1
-------------------------------
123,345,567,789,980
111,222,333

i want to write a query and select something like this

c1
-------------------------------
123
345
567
789
980
111
222
333

Thanks alot in advance,
Pal

Tom Kyte
January 09, 2004 - 8:11 am UTC

either of the following, probably #2 is best but as always -- test test test 

ops$tkyte@ORA817DEV> create table t ( a int, x varchar2(40) );
 
Table created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into t values ( 1, '123,345,567,789,980' );
 
1 row created.
 
ops$tkyte@ORA817DEV> insert into t values ( 2, '111,222,333' );
 
1 row created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select a, substr(x, instr(x,',',1,rr.r)+1, instr(x,',',1,rr.r+1)-instr(x,',',1,rr.r)-1 ) x
  2    from
  3  (select a, ','||x||',' x, length(x)-length(replace(x,',',''))+1 cnt
  4     from t ) t,
  5  (select rownum r
  6     from all_objects
  7    where rownum <= 50) rr
  8   where rr.r <= t.cnt
  9   order by 1, 2
 10  /
 
         A X
---------- ------------------------------------------
         1 123
         1 345
         1 567
         1 789
         1 980
         2 111
         2 222
         2 333
 
8 rows selected.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace type myTable as table of varchar2(25)
  2  /
 
Type created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace function str2tbl( p_str in varchar2 ) return myTable
  2  as
  3      l_str   long default p_str || ',';
  4      l_n     number;
  5      l_data  myTable := myTable();
  6  begin
  7      loop
  8          l_n := instr( l_str, ',' );
  9          exit when (nvl(l_n,0) = 0);
 10          l_data.extend;
 11          l_data(l_data.count) := ( ltrim(rtrim(substr(l_str,1,l_n-1))) );
 12          l_str := ltrim( substr( l_str, l_n+1 ) );
 13      end loop;
 14      return l_data;
 15  end;
 16  /
 
Function created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select a, column_value
  2    from t, TABLE( cast(str2tbl(x) as myTable) ) x
  3  /
 
         A COLUMN_VALUE
---------- -------------------------
         1 123
         1 345
         1 567
         1 789
         1 980
         2 111
         2 222
         2 333
 
8 rows selected.
 
 

German Decimal notation

Yogesh, January 12, 2004 - 3:50 am UTC


In German, decimal notation is contrary to the British one. They use decimal points as separators for thousands and decimal comma as separator for the non-integer part of the number.

If we define a datatype as number and we need to handle above stuff, what approach should be used ?

Tom Kyte
March 04, 2008 - 7:16 am UTC

no problems, database sort of "does that"

decimals and commas are just formats... they are not really "stored" with the number..

consider:

ops$tkyte@ORA9IR2> alter session set NLS_NUMERIC_CHARACTERS='.,';
 
Session altered.
<b>thats american (and others...)</b>
 
ops$tkyte@ORA9IR2> select 1,2 , '1,2',
  2         to_char( x, '999g999d999' ), to_char(x, '999,999.999' )
  3    from t;
 
         1          2 '1, TO_CHAR(X,'9 TO_CHAR(X,'9
---------- ---------- --- ------------ ------------
         1          2 1,2  123,456.789  123,456.789

<b>if you use G and D, you get the group separator and decimal point -- here they are the "same"</b>

 
ops$tkyte@ORA9IR2> select 1,2 , to_number( '1,2' ),
  2         to_char( x, '999g999d999' ), to_char(x, '999,999.999' )
  3    from t;
select 1,2 , to_number( '1,2' ),
                        *
ERROR at line 1:
ORA-01722: invalid number
 
<b>and 1,2 is not a number in this case...</b>
 
ops$tkyte@ORA9IR2> insert into t values ( 1.2 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1,2 );
insert into t values ( 1,2 )
            *
ERROR at line 1:
ORA-00913: too many values
 
<b>this is to demonstrate a certain ambiguity that'll crop up</b>
 
ops$tkyte@ORA9IR2> insert into t values ( '1,2' );
insert into t values ( '1,2' )
                       *
ERROR at line 1:
ORA-01722: invalid number
 
 
ops$tkyte@ORA9IR2> rollback;
 
Rollback complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter session set NLS_NUMERIC_CHARACTERS=',.';
 
Session altered.
 
<b>this is your format..</b>

ops$tkyte@ORA9IR2> select 1,2 , '1,2',
  2         to_char( x, '999g999d999' ), to_char(x, '999,999.999' )
  3    from t;
 
         1          2 '1, TO_CHAR(X,'9 TO_CHAR(X,'9
---------- ---------- --- ------------ ------------
         1          2 1,2  123.456,789  123,456.789

<b>see the difference -- ., insetead of ,.</b>
 
ops$tkyte@ORA9IR2> select 1,2 , to_number( '1,2' ),
  2         to_char( x, '999g999d999' ), to_char(x, '999,999.999' )
  3    from t;
 
         1          2 TO_NUMBER('1,2') TO_CHAR(X,'9 TO_CHAR(X,'9
---------- ---------- ---------------- ------------ ------------
         1          2              1,2  123.456,789  123,456.789

<b>but see how 1,2 are two COLUMNS, not 1,2 the number -- you need to "string" it... that is the ambiguity there</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1.2 );
 
1 row created.

<b>(I'm sitting next to a guy that wrote the NLS course) according to him, the parser has . and comma hard coded - so that is really "1,2" but...</b> 

ops$tkyte@ORA9IR2> insert into t values ( 1,2 );
insert into t values ( 1,2 )
            *
ERROR at line 1:
ORA-00913: too many values
 
<b>1,2 is two columns again so ...</b>
 
ops$tkyte@ORA9IR2> insert into t values ( '1,2' );
 
1 row created.

<b>that goes there...</b>

It is all about the formats (and the sql parser which must tokenize things -- hence the hard coded "." and "," meaning on parse) 

 

Great Explaination

Yogesh, January 21, 2004 - 3:14 am UTC

Thanks for the explaination tom, I can even set NLS_NUMERIC_CHARACTERS at instance level, right ? But is there any way by which I can refer 1,2 as 1.2 without using to_char ?

Tom Kyte
January 21, 2004 - 6:45 am UTC

you can set things at the instance level HOWEVER if the client sets any NLS_* parameter, they will all default for that client -- so, in practicality, no, you cannot really set things at the instance level (and expect them to be in place by default).

a login trigger can help you.


consider this:

insert into t values ( 1,2 )
select 1,2 from dual;


is that inserting one or two columns into T? See the ambiguity -- you have to use "to_number" on a string if you want to use literals with ","'s in it like that.

Number-in-a-VARCHAR2: Any Holes in This Approach?

Mac, January 21, 2004 - 10:02 am UTC

This function seems to work. Anybody see any potential problem?






CREATE OR REPLACE FUNCTION is_numeric
  (p_string IN VARCHAR2)
RETURN VARCHAR2
IS
  l_nv_err EXCEPTION;
  l_number NUMBER;
  l_result VARCHAR2(1) := 'N';
  PRAGMA EXCEPTION_INIT(l_nv_err, -6502);
BEGIN
  BEGIN
    l_number := TO_NUMBER(p_string);
    l_result := 'Y';
  EXCEPTION
    WHEN invalid_number THEN
      l_result := 'N';
    WHEN l_nv_err THEN
      l_result := 'N';
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20001, 'Error in is_numeric: '||SQLERRM);
  END;
  --
  RETURN(l_result);
END;
/
 
 
 
SQL> select*from dual where is_numeric('12345')='Y';

D
-
X
 
 
 
SQL> select*from dual where is_numeric(12345)='Y';

D
-
X
 
 
 
SQL> select*from dual where is_numeric('1234A')='Y';

no rows selected 

Tom Kyte
January 21, 2004 - 3:03 pm UTC

it works, much in the same fashion as above...

I just assumed ANY error converting the string to a number meant 'not a number'

String To Number Conversion

Denise, February 09, 2004 - 9:49 am UTC

Hi Tom

We have an Address Table with the Zip Column as a Varchar2(10).

We also have a package that does matching records with the
first 5 digits of the zip code(zip column).

We noticed that when the first digit of the zip code was
zero(0)...it was being trimmed off (see below):
SQL> select zip from address where idnumber=1234;

ZIP
----------
06826-4204


SQL> declare
  2  zipc number(5);
  3  begin
  4  select substr(zip,1,5) into zipc
  5  from address
  6  where idnumber=1234;
  7  
  8  dbms_output.put_line(zipc);
  9  end;
 10  /
6826
************************************************************
I tried several scenarios trying to convert the zip column
to a number then assigning the value to a number variable
(zipc)...keep getting the ora-01722 error.

SQL> declare
  2  zipc varchar2(5);
  3  begin
  4  select to_number(substr(zip,1,5)) into zipc
  5  from address
  6  where idnumber=1234;
  7  dbms_output.put_line(zipc);
  8  end;
  9  /
6826

PL/SQL procedure successfully completed.

SQL> 
  1  declare
  2  zipc number(5);
  3  begin
  4  select to_number(substr(zip,1,5)) into zipc
  5  from address
  6  where idnumber=1234;
  7  dbms_output.put_line(zipc);
  8* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 4

SQL> declare
  2  zipc number(5);
  3  begin
  4  select to_number(zip,'99999') into zipc
  5  from address
  6  where idnumber=1234;
  7  dbms_output.put_line(zipc);
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 4
************************************************************
perhaps this is the wrong approach to solving the issue
of why leading zero(s) are being trimmed off.

why isn't the to_number function working here?
you mention replace and deterministic..are those functions
that can be applied here?

thanks!

 

Tom Kyte
February 09, 2004 - 9:58 am UTC

it is not getting "stripped off"

NUMBERS (in any sense of the word) do not have leading zeroes. When you get a check does it read

$000,000,000,000,000,123.21

or does it read

$123.21

leading zeroes on number types "don't make sense".


Because you convert this into a number, the zeroes (which make sense in a character string) "go away"



So, my question to you -- why are you trying to put this string data (it must be string data since you want the string unmodified here) into a number in the first place????

point well taken....

Denise, February 09, 2004 - 10:15 am UTC

the problem we are encountering is that records that are
being imported(and run through this package) are not being
matched correctly because of the leading zero issue.
When we run the unmatched reports we are seeing:

John Doe 123 Main Street 06842-XXXX (in the system)
John Doe 123 Main Street 6842-XXXX(from the import file)

We are matching on last name, line1 and zip(first 5 characters) and John Doe does not get included in the matching because the leading zero is not there(for want of a better description). We have verfied this through the
reports.

My logic in trying to convert the string to a number was
based on a zero being numerically valid unless you
explicity insert code to remove leading zero's.

This is not a package I wrote it's part of the Software
Application.

Tom Kyte
February 09, 2004 - 10:31 am UTC

lpad it

lpad( zip, '0', 10 )

thanks Tom!...works perfectly!!!!

Denise, February 09, 2004 - 10:50 am UTC

SQL> declare
  2  zipc varchar2(5);
  3  begin
  4  select lpad(zip,5,'0') into zipc
  5  from address
  6  where idnumber=1234;
  7  dbms_output.put_line(zipc);
  8  end;
  9  /
06826

PL/SQL procedure successfully completed.
 

Nice

Steve, February 25, 2004 - 1:00 am UTC

Hi Tom,
Is it possible in Oracle to get the maximum positive number
and maximum negative number?Are there any constants like
POSITIVE_INFINITY and NEGATIVE_INFINITY in Oracle?I wish to know this.Please do reply.
Thanks in advance.

Tom Kyte
February 25, 2004 - 9:03 am UTC

other than the documented limits, no, there is no "constant". you could certainly create a package with constants in it for your own use.

OK

Siva, March 26, 2004 - 7:55 am UTC

Hi Tom,
Oracle documentation says "Positive numbers can take a max.
value of 9.999...*10 raised to 125 and for negative numbers
as 1.0*10 raised to -130".Is there any way in Oracle to
retrieve such values?I think Mr.Steve was referring like this.*I mean,Any programmatic way to retrieve such values Please do reply.
Bye!


Tom Kyte
March 26, 2004 - 9:30 am UTC

sure, just select them?

ops$tkyte@ORA9IR1> select 9.999 * power(10,125) from dual;
 
9.999*POWER(10,125)
-------------------
         9.999E+125
 
ops$tkyte@ORA9IR1> select power(10,-130) from dual;
 
POWER(10,-130)
--------------
    1.000E-130


but they are NOT +- infinity, they are "really numbers" 

Looking for an approach to reduce the overhead of function call

Matt, March 28, 2004 - 6:00 pm UTC

I have some data with a field varchar2(10). I need to query this data and generate a computed value based on this field. There may be many of these fields in the result set with the same value. I am looking for ways of reducing the overhead of the PL/SQL calls.

My options appear to be:

i) Remove the function and do it all in SQL
ii) Reduce the number of function calls
iii) Cache values calculated by the function to reduce the computation required for duplicate field values.
iv) Something I haven't thought of.

So,

i) I implemented the algorithm in SQL and it ran significantly slower.
ii) I can't remove any PL/SQL calls since each row needs to have the computed value calculated (a function call)
iii) I am considering generating a package to store those values already calculated. The function would need to carry out a cache lookup prior to doing any of the computation. This should reduce some of the required work.
iv) I am looking for alternative approaches, hence this post.

Can you please comment on the above? In particular I am looking for alternative approaches that I have not considered.

THanks,

Tom Kyte
March 28, 2004 - 6:46 pm UTC

i) that i don't believe -- show me the before and after please! somethings up there.



Here arethe two implementations...

Matt, March 28, 2004 - 10:28 pm UTC

--
drop table test_data
/
create table test_data (id varchar2(10), id_check number(1))
/
insert into test_data values ('5600012357',9)
/
insert into test_data values ('5500000278',4)
/
insert into test_data values ('5500003074',5)
/
insert into test_data values ('5500008129',2)
/
insert into test_data values ('5500012357',8)
/
insert into test_data values ('5500023478',0)
/
insert into test_data values ('5500047359',4)
/
insert into test_data values ('5500067253',5)
/
insert into test_data values ('5500079467',6)
/
insert into test_data values ('5500089000',8)
/
insert into test_data values ('5500099352',6)
/
insert into test_data values ('5500102781',5)
/
insert into test_data values ('5500139654',8)
/
insert into test_data values ('5500200000',4)
/
insert into test_data values ('5500289367',3)
/
insert into test_data values ('5500346583',7)
/
insert into test_data values ('5600000278',2)
/
insert into test_data values ('5600003074',3)
/
insert into test_data values ('5600008129',0)
/
insert into test_data values ('5600012357',9)
/
insert into test_data values ('5600023478',8)
/
insert into test_data values ('5600047359',2)
/
insert into test_data values ('5600067253',3)
/
insert into test_data values ('5600079467',4)
/
insert into test_data values ('5600089000',6)
/
insert into test_data values ('5600099352',4)
/
insert into test_data values ('5600102781',3)
/
insert into test_data values ('5600139654',6)
/
insert into test_data values ('5600200000',2)
/
insert into test_data values ('5600289367',1)
/
insert into test_data values ('5600346583',5)
/
commit
/

create or replace function gen_check(p_id in varchar2) return pls_integer
deterministic is
-- l_result pls_integer;
i pls_integer :=0;
d pls_integer :=0;
l_buffer varchar2 (50) := '';
l_val pls_integer :=0;
l_multiply boolean := true;
l_check pls_integer := 0;
begin
if p_id is not null then
l_buffer := UPPER(p_id);
i := length(p_id);

l_multiply := TRUE;
WHILE i > 0 LOOP

d := ascii(substr(l_buffer,i,1));

IF (l_multiply = TRUE) THEN
d := d*2;
END IF;
l_multiply := NOT l_multiply;

WHILE d > 0 LOOP
--dbms_output.PUT_LINE('d='||d);
--dbms_output.PUT_LINE('mod(d,10)='||MOD(d,10));
l_val := l_val + MOD(d,10);
--dbms_output.PUT_LINE('val='||val);
d := floor(d / 10);
END LOOP;
i := i - 1;

END loop;
--dbms_output.PUT_LINE('val='||val);
l_check := MOD((10 - MOD(l_val,10)),10);

/*
select mod((10 - mod(sum(padded_val_ch),10)),10) into l_check
from ( select sum(substr(padded_val,length(padded_val)- row_num + 1,1)) as padded_val_ch
-- ,pos
-- ,doubled_val
from ( select pos
-- ,chr
-- ,ascii_val
,doubled_val
,lpad(doubled_val,5,'0') as padded_val
,row_number() over (partition by pos,doubled_val order by doubled_val ) row_num
from ( select rownum as pos
-- ,substr(p_id,length(p_id) - rownum + 1,1) as chr
-- ,ascii(substr(p_id,length(p_id) - rownum + 1,1)) as ascii_val
-- ,case when mod(rownum,2) = 1 then 'Y'
-- else 'N' end to_double
,case when mod(rownum,2) = 1 then 2*ascii(substr(p_id,length(p_id)- rownum + 1,1))
else ascii(substr(p_id,length(p_id)- rownum + 1,1)) end as doubled_val
from table(vtable(length(p_id))) )
,table(vtable(5)) )
group by pos, doubled_val );
*/
else
null;
end if;
return l_check;
-- return l_result;
end gen_check;
/

create type array
as table of number
/

create or replace function vtable (n in number default null)
return array
pipelined
as
begin
for i in 1.. nvl(n,999999999)
loop
pipe row(i);
end loop;
return;
end;
/

create or replace function gen_check_sql(p_id in varchar2) return pls_integer
deterministic is
l_check pls_integer := 0;
begin
if p_id is not null then
select mod((10 - mod(sum(padded_val_ch),10)),10) into l_check
from ( select sum(substr(padded_val,length(padded_val)- row_num + 1,1)) as padded_val_ch
-- ,pos
-- ,doubled_val
from ( select pos
-- ,chr
-- ,ascii_val
,doubled_val
,lpad(doubled_val,5,'0') as padded_val
,row_number() over (partition by pos,doubled_val order by doubled_val ) row_num
from ( select rownum as pos
-- ,substr(p_id,length(p_id) - rownum + 1,1) as chr
-- ,ascii(substr(p_id,length(p_id) - rownum + 1,1)) as ascii_val
-- ,case when mod(rownum,2) = 1 then 'Y'
-- else 'N' end to_double
,case when mod(rownum,2) = 1 then 2*ascii(substr(p_id,length(p_id)- rownum + 1,1))
else ascii(substr(p_id,length(p_id)- rownum + 1,1)) end as doubled_val
from table(vtable(length(p_id))) )
,table(vtable(5)) )
group by pos, doubled_val );
else
null;
end if;
return l_check;
-- return l_result;
end gen_check_sql;
/

select t.id
,t.id_check tbl_val
,gen_check(t.id) calc_val
,case when t.id_check = gen_check(t.id) then 'SUCCESS'
else 'FAILURE'
end
from test_data t
/

select t.id
,t.id_check tbl_val
,gen_check(t.id) calc_val
,case when t.id_check = gen_check_sql(t.id) then 'SUCCESS'
else 'FAILURE'
end
from test_data t
/

Over the small data set provided the two implementations are comparable. When run against 500,000 rows the SQL implementation was significantly slower - I don't have that data to hand.

My conclusion was that the SQL implementation was more CPU expensive since it has to loop more times for each "add up all the digits" computation (as SQL does not know when it has processed all the digits that it loops through)

At the point where I realised the PL/SQL routine was faster I stopped looking at incorporating the SQL in the larger query. As I expected that this result would carry through to an 'entirely in SQL approach'.

I now expect you will tell me that the all in SQL will be the fastest implementation.



Tom Kyte
March 29, 2004 - 9:39 am UTC

Now I understand -- you never ever did a pure SQL implementation.  You just have two plsql functions :)

Ok, assuming we can assume a max length on the input (i chose 10 from your example), we can take a function like this:

ops$tkyte@ORA9IR2> @mystat "CPU used by this session"
ops$tkyte@ORA9IR2> column value new_val V
ops$tkyte@ORA9IR2> define S="&1"
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&S')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&S')||'%'
new   4: and lower(a.name) like '%' || lower('CPU used by this session')||'%'
 
NAME                                VALUE
------------------------------ ----------
CPU used by this session             3764
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly
ops$tkyte@ORA9IR2> select id, id_check, gen_check(id) plsql
  2    from test_data
  3  /
 
10031 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST_DATA'
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        695  consistent gets
          0  physical reads
          0  redo size
     262962  bytes sent via SQL*Net to client
       7847  bytes received via SQL*Net from client
        670  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10031  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set linesize 121
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> select a.name, b.value V, b.value-&V diff
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&S')||'%'
  5  /
old   1: select a.name, b.value V, b.value-&V diff
new   1: select a.name, b.value V, b.value-      3764 diff
old   4: and lower(a.name) like '%' || lower('&S')||'%'
new   4: and lower(a.name) like '%' || lower('CPU used by this session')||'%'
 
NAME                                    V       DIFF
------------------------------ ---------- ----------
CPU used by this session             3846         82

<b>running in about 0.82 cpu seconds for 10,000 records and use SQL like this</b>

ops$tkyte@ORA9IR2> select id, id_check, mod((10-mod(x,10)),10) sql
  2    from (
  3  select substr(c1,1,1)+substr(c1,2,1)+substr(c1,3,1)+
  4         substr(c2,1,1)+substr(c2,2,1)+substr(c2,3,1)+
  5         substr(c3,1,1)+substr(c3,2,1)+substr(c3,3,1)+
  6         substr(c4,1,1)+substr(c4,2,1)+substr(c4,3,1)+
  7         substr(c5,1,1)+substr(c5,2,1)+substr(c5,3,1)+
  8         substr(c6,1,1)+substr(c6,2,1)+substr(c6,3,1)+
  9         substr(c7,1,1)+substr(c7,2,1)+substr(c7,3,1)+
 10         substr(c8,1,1)+substr(c8,2,1)+substr(c8,3,1)+
 11         substr(c9,1,1)+substr(c9,2,1)+substr(c9,3,1)+
 12         substr(c10,1,1)+substr(c10,2,1)+substr(c10,3,1) x,
 13         id, id_check
 14    from (
 15  select decode( mult, 2,   c1, 2*c1 ) c1,
 16         decode( mult, 2, 2*c2,   c2 ) c2,
 17         decode( mult, 2,   c3, 2*c3 ) c3,
 18         decode( mult, 2, 2*c4,   c4 ) c4,
 19         decode( mult, 2,   c5, 2*c5 ) c5,
 20         decode( mult, 2, 2*c6,   c6 ) c6,
 21         decode( mult, 2,   c7, 2*c7 ) c7,
 22         decode( mult, 2, 2*c8,   c8 ) c8,
 23         decode( mult, 2,   c8, 2*c8 ) c9,
 24         decode( mult, 2,2*c10,  c10 ) c10,
 25         id, id_check
 26    from (
 27  select ascii(substr(x,1,1)) c1,
 28         ascii(substr(x,2,1)) c2,
 29         ascii(substr(x,3,1)) c3,
 30         ascii(substr(x,4,1)) c4,
 31         ascii(substr(x,5,1)) c5,
 32         ascii(substr(x,6,1)) c6,
 33         ascii(substr(x,7,1)) c7,
 34         ascii(substr(x,8,1)) c8,
 35         ascii(substr(x,9,1)) c9,
 36         ascii(substr(x,10,1)) c10,
 37         decode( mod(length(x),2), 0, 2, 1 ) mult,
 38         id, id_check
 39    from ( select id, id_check, upper(id) x from test_data )
 40         )
 41         )
 42         )
 43  /
 
10031 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST_DATA'
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        695  consistent gets
          0  physical reads
          0  redo size
     268444  bytes sent via SQL*Net to client
       7847  bytes received via SQL*Net from client
        670  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10031  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> select a.name, b.value V, b.value-&V diff
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&S')||'%'
  5  /
old   1: select a.name, b.value V, b.value-&V diff
new   1: select a.name, b.value V, b.value-      3846 diff
old   4: and lower(a.name) like '%' || lower('&S')||'%'
new   4: and lower(a.name) like '%' || lower('CPU used by this session')||'%'
 
NAME                                    V       DIFF
------------------------------ ---------- ----------
CPU used by this session             3856         10


<b>running consistently 8 times faster....</b>


If you need more than 10, the "algorithm" should be pretty visible there.  I just diagnosed what your code did and "flatlined" it. 

Great!

Matt, March 29, 2004 - 5:11 pm UTC

Thanks Tom.

I think that if I had continued on and generated a full SQL version I wouldn't have "flatlined" the SQL quite like you have (and may therefore have ended up with a less efficient implementation).

I think it is a rather neat solution really, almost like a procedural SQL implementation.

Thanks again.

Just for completeness..

Matt, March 29, 2004 - 9:59 pm UTC

The final query.

Fixed:

- a minor typo in the "mult" part
- addition of nvl (as substr appears to return null when looking past the end of the string).

Thanks again.

select id, id_check, mod((10-mod(x,10)),10) sql
from (
select nvl(substr(c1,1,1),0)+nvl(substr(c1,2,1),0)+nvl(substr(c1,3,1),0)+
nvl(substr(c2,1,1),0)+nvl(substr(c2,2,1),0)+nvl(substr(c2,3,1),0)+
nvl(substr(c3,1,1),0)+nvl(substr(c3,2,1),0)+nvl(substr(c3,3,1),0)+
nvl(substr(c4,1,1),0)+nvl(substr(c4,2,1),0)+nvl(substr(c4,3,1),0)+
nvl(substr(c5,1,1),0)+nvl(substr(c5,2,1),0)+nvl(substr(c5,3,1),0)+
nvl(substr(c6,1,1),0)+nvl(substr(c6,2,1),0)+nvl(substr(c6,3,1),0)+
nvl(substr(c7,1,1),0)+nvl(substr(c7,2,1),0)+nvl(substr(c7,3,1),0)+
nvl(substr(c8,1,1),0)+nvl(substr(c8,2,1),0)+nvl(substr(c8,3,1),0)+
nvl(substr(c9,1,1),0)+nvl(substr(c9,2,1),0)+nvl(substr(c9,3,1),0)+
nvl(substr(c10,1,1),0)+nvl(substr(c10,2,1),0)+nvl(substr(c10,3,1),0) x,
id, id_check
from (
select decode( mult, 2, c1, 2*c1 ) c1,
decode( mult, 2, 2*c2, c2 ) c2,
decode( mult, 2, c3, 2*c3 ) c3,
decode( mult, 2, 2*c4, c4 ) c4,
decode( mult, 2, c5, 2*c5 ) c5,
decode( mult, 2, 2*c6, c6 ) c6,
decode( mult, 2, c7, 2*c7 ) c7,
decode( mult, 2, 2*c8, c8 ) c8,
decode( mult, 2, c9, 2*c8 ) c9,
decode( mult, 2,2*c10, c10 ) c10,
id, id_check
from (
select ascii(substr(x,1,1)) c1,
ascii(substr(x,2,1)) c2,
ascii(substr(x,3,1)) c3,
ascii(substr(x,4,1)) c4,
ascii(substr(x,5,1)) c5,
ascii(substr(x,6,1)) c6,
ascii(substr(x,7,1)) c7,
ascii(substr(x,8,1)) c8,
ascii(substr(x,9,1)) c9,
ascii(substr(x,10,1)) c10,
decode( mod(length(x),2), 0, 2, 1 ) mult,
id, id_check
from ( select id, id_check, upper(id) x from test_data )
)
)
)

how to validate

A reader, May 26, 2004 - 12:06 pm UTC

how to validate and see if a number value is negative.

Tom Kyte
May 26, 2004 - 3:33 pm UTC

check (x < 0 and x is not null)

on the column in the create table statement, or in an alter statment.

assuming you meant a database column of course, in code it is just


if ( x >= 0 or x is null )
then
error.

my question was specific to a select

A reader, May 26, 2004 - 9:42 pm UTC

I have to identify negative numbers and apped a '-' the value. I have to to do this from within a SELECT and not pl/sql code.

How can we do it in sql select ?

Tom Kyte
May 27, 2004 - 8:55 am UTC

if the number is negative, it'll just happen. try it.

or give me a concrete example to work with and say "this is what we have, this is what I get, this is what i want"

Short circuit

A reader, December 06, 2004 - 5:56 pm UTC

SQL> create table t(i varchar2(10));

Table created.

SQL> insert into t values (1);

1 row created.

SQL> insert into t values(2);

1 row created.

SQL> insert into t values ('3a');

1 row created.

SQL> select * from t;

I
----------
1
2
3a

SQL> select decode(replace(translate(tmpvalue,'1234567890.','00000000000'),'0',''),null,to_number(
  2  
SQL> select decode(replace(translate(i,'1234567890.','00000000000'),'0',''),null,to_number(i),i) from t;
ERROR:
ORA-01722: invalid number



no rows selected

Why does this error out? I thought that DECODE short circuits i.e. it calls the to_number() only when 'i' contains all digits and .?

Thanks 

Tom Kyte
December 07, 2004 - 9:53 am UTC

that decode( f(x), NULL, to_number(i), i )

is identical to

decode( f(x), NULL, to_number(i), to_number(i) )


the first return value dictates the overloaded decode function that will be used. your first return value was a NUMBER, hence you are using the decode that returns a NUMBER, hence all of the return values are implicitly to_number'ed.




DECODE()

A reader, December 07, 2004 - 10:12 am UTC

Ah, I see then I guess the following will work

SQL> create table t(i varchar2(10));

Table created.

SQL> insert into t values ('1');

1 row created.

SQL> insert into t values ('2.00')
  2  /

1 row created.

SQL> insert into t values ('3a');

1 row created.

SQL> insert into t values ('100.0000');

1 row created.

SQL> select case when replace(translate(i,'0123456789. ','000000000000'),'0','') is null then to_char(to_number(i)) else i end
  2  from t;

CASEWHENREPLACE(TRANSLATE(I,'0123456789.
----------------------------------------
1
2
3a
100

In other words, if it "looks" like a number, convert it to a number and to_char() it otherwise return the "bad" string.

Thanks! 

pruning?!

Gabe, January 06, 2005 - 9:27 am UTC

Since there is no order of operation for the sql predicates Â… for your original test case and answer shouldnÂ’t we do something like Â…

select iv.*
from (select rownum, t.*
from t
where <all the other predicates>
) iv
where decode(replace(translate(tmpvalue,'1234567890.'
,'00000000000'),'0','')
,null, my_to_number(tmpvalue),0 ) = 0
;

short-circuiting the my_to_number() function is a neat trick Â… shouldnÂ’t we also make sure to prune as many rows as possible before applying that numeric checking predicate?


Tom Kyte
January 06, 2005 - 11:02 am UTC

not in general, no -- that rownum could/would cause "big temp area usage".

if <all the other predicates> filtered out a trivial amount of rows, perhaps -- but in "general", no -- i would not do that.

Some gaps in my understanding ...

Gabe, January 06, 2005 - 11:24 am UTC

<quote>if <all the other predicates> filtered out a trivial amount of rows, perhaps</quote>

I would've thought exactly the opposite ... if the inline predicates would've pruned the majority of rows, the rownum impact would've been relatively modest. Isn't it the rownum count the last thing to happen in the processing of the inline view?

Tom Kyte
January 06, 2005 - 11:48 am UTC

should have been more clear, sorry:

if the <all the other predicates> filtered in such a fashion that only a trivial amount of rows were output

if the predicates were very very selective.


I can see how my statement could easily be confused.

One more comment ...

Gabe, January 06, 2005 - 2:47 pm UTC

Ok Â… that makes sense Â… I was about to send a test case showing an order of magnitude difference in elapsed time.

I found that predicating "where my_to_number(tmpvalue) = 0" is faster than your approach as long as short-circuiting happens for less than 30% of the records. In other words, if the majority of rows (+70%) are in fact (candidate) numbers than just applying the function to the whole data set is faster. Of course, my_to_number() would have to be changed to do "to_number(p_num,'999999999.99')" in order to eliminate false positives for strings _appearing_ to be numbers in scientific notation i.e. '2e3'. The _cleaner_ the data the less appealing that decode/replace/translate/to_number implementation seems to be.


Integer value in varchar2 field

sonali, May 24, 2005 - 9:46 am UTC

I have following query -

select SET_REG_VALUE
from mwebsettings
where SET_REG_VALUE =3;

SET_REG_VALUE is varchar2(100)

In 9i on Unix machine it gives me error
ERROR:
ORA-01722: invalid number

In 9i on windows machine it works.. Why doesn't it work on unix server ?

I thought you can compare integer value in varchar2 field without quotes, or was I wrong ?

Thanks

Tom Kyte
May 24, 2005 - 1:00 pm UTC

because you don't have a number in that field and you are comparing it to a number and a string compared to a number is really


to_number(string) = number

it won't work on windows either if you insert into mwebsettings (set_reg_value) values ( 'hello world' );

compare STRINGS to STRINGS
numbers to numbers
and dates to dates and use no other compares.

first_load_ti

Joachim Seekopp, May 24, 2005 - 1:40 pm UTC

thank you.

by the way,why is first_load_time in v$sqlarea
a varchar2(57) and not a date ?

Tom Kyte
May 24, 2005 - 1:52 pm UTC

no idea, we'd have to ask the person that did it :)

String translation

Yiming, January 31, 2006 - 1:19 pm UTC

Hi, tom,
Since I knew this web site. I have learned a lot. Right now I have a difficulty to get the query that a little similar to Mr, Pal. The data in the column is x.x-x.x format. x= 1 to 10. I want to get the query which format likes x.x, x,x, x.x,... I have 10g. The example like:

table t
Column1
--------------
10.5-10.10

I want to get query like
Column1
-------------------------------
10.5,10.6,10.7,10.8,10.9,10.10


Thank you for your help


Tom Kyte
January 31, 2006 - 3:40 pm UTC

well, I would say it is not similar at all. (they wanted to turn a comma delimited string into a set, you want to reformat a single string into some other single string)


Looks like it would be easiest for you to write a plsql function that takes that string as input, parses it, formats the output string and returns it.

A solution

Michel Cadot, February 01, 2006 - 2:41 am UTC

Hi,

Here's one way to do it.

SQL> select * from t;
COL
--------------------
10.5-10.10

1 row selected.

SQL> with 
  2    a as (
  3      select col, 
  4             to_number(substr(col,1,instr(col,'.')-1)) valb,
  5             to_number(substr(col,instr(col,'.')+1,
  6                                  instr(col,'-')-instr(col,'.')-1)) val1,
  7             to_number(substr(col,instr(col,'.',1,2)+1)) val2
  8      from t
  9    )
 10  select col, 
 11         max(substr(sys_connect_by_path(val,','),2)) val
 12  from ( select col,
 13                to_char(valb)||'.'||to_char(val1+rn) val,
 14                row_number () over (partition by col order by rn) curr,
 15                row_number () over (partition by col order by rn)-1 prev
 16         from ( select col, valb, val1, rownum-1 rn 
 17                from dual, a 
 18                connect by level <= a.val2-a.val1+1 )
 19       )
 20  connect by prior curr = prev and prior col = col
 21  start with curr = 1
 22  group by col
 23  order by col
 24  /
COL                  VAL
-------------------- ---------------------------------------
10.5-10.10           10.5,10.6,10.7,10.8,10.9,10.10

1 row selected.

Regards
Michel

 

Further more complex

Yiming, February 01, 2006 - 5:08 pm UTC

Thanks tom and micheal for your help.

If the query more complicated like these two formats mixed together like x.x, x.x-x.x, x.x-x.x, x.x , then translate to x.x,x.x,x.x,x.x,x.x...
example:
col
---------------------
9.9, 8.1-8.3, 7.2-7.5, 2.6

I want like
col
----------------------
9.9,8.1,8.2,8.3,7.2,7.3,7.4,7.5,2.6

Thanks in advance



Tom Kyte
February 02, 2006 - 4:31 am UTC

I will reiterate that to me this looks like you want to write a piece of code, in a function, using plsql, that reformats this. It would be the most intuitive, understandable and maintainable approach.

We can do a neat query to do this in sql (simple variation on the above query), but I think a clean function in this case would be better.

yiming, February 02, 2006 - 1:11 pm UTC

Thanks Tom

I will try to write a function. I think this is a more complicated function.

yiming

Reformat

Yiming, February 06, 2006 - 10:56 am UTC

Hi, Tom,

With your clue, I build a function.

scott@TEST1> CREATE OR REPLACE FUNCTION REFORMAT( P_STMT IN VARCHAR2) RETURN VARCHAR2
2 AUTHID CURRENT_USER AS
3 TYPE C_REF_CUR IS REF CURSOR;
4 V_STR VARCHAR2(4000);
5 V_COLVAL VARCHAR2(4000);
6 C C_REF_CUR;
7 L NUMBER;
8 V_ROW NUMBER;
9 V_COL1 NUMBER;
10 V_COL2 NUMBER;
11 V_COL_GAP NUMBER;
12 BEGIN
13 OPEN C FOR P_STMT;
14 LOOP
15 FETCH C INTO V_COLVAL;
16 EXIT WHEN C %NOTFOUND;
17 V_ROW :=TO_NUMBER(SUBSTR(V_COLVAL, 1, INSTR(V_COLVAL, '.',1,1) - 1));
18 V_COL1 :=TO_NUMBER(SUBSTR(V_COLVAL,
19 INSTR(V_COLVAL,'.',1,1)+1,(INSTR(V_COLVAL, '-',1,1)) -
20 (INSTR(V_COLVAL,'.',1,1)+1) ));
21 V_COL2 :=TO_NUMBER(SUBSTR(V_COLVAL, INSTR(V_COLVAL,'.',1,2)+1));
22 V_COL_GAP := V_COL2 - V_COL1;
23 V_STR:= V_STR || ',' || TO_CHAR(V_ROW) || '.' || TO_CHAR(V_COL1);
24 FOR I IN 1..V_COL_GAP LOOP
25 V_COL1 := V_COL1 + 1;
26 V_STR:= V_STR || ',' || TO_CHAR(V_ROW) || '.' || TO_CHAR(V_COL1) ;
27 END LOOP;
28 END LOOP;
29 CLOSE C;
30 RETURN SUBSTR(V_STR,2);
31
32 END;
33 /

Function created.

scott@TEST1> CREATE TABLE T (ID NUMBER, COL VARCHAR2(200));

Table created.

scott@TEST1> DROP TABLE T;

Table dropped.

scott@TEST1>
scott@TEST1> CREATE TABLE T (ID NUMBER, COL VARCHAR2(200));

Table created.

scott@TEST1>
scott@TEST1> INSERT INTO T VALUES(1, '10.1-10.5');

1 row created.

scott@TEST1>
scott@TEST1> INSERT INTO T VALUES(2, '2.1-2.10');

1 row created.

scott@TEST1>
scott@TEST1> INSERT INTO T VALUES(3, '5.5-5.7');

1 row created.

scott@TEST1>
scott@TEST1> COMMIT;

Commit complete.

scott@TEST1> set line 5000

scott@TEST1> SELECT * FROM T;

ID COL
---------- ----------------------------
1 10.1-10.5
2 2.1-2.10
3 5.5-5.7

1 SELECT DISTINCT a.id,
2 reformat('SELECT col FROM t WHERE id = ' || '''' || a.id || ''''
3 || ' ORDER BY id') AS t1
4 FROM t a
5* ORDER BY ID
scott@TEST1> /

ID T1
---------- ------------------------------------------------------------------
1 10.1,10.2,10.3,10.4,10.5
2 2.1,2.2,2.3,2.4,2.5,2.6,2.7,2.8,2.9,2.10
3 5.5,5.6,5.7

thanks tom,

yiming


Sorry

Michel Cadot, February 08, 2006 - 2:56 am UTC

Hi Yiming, Tom,

I can't resist to a SQL puzzle more than a week. :)

SQL> select * from t;
        ID COL
---------- ------------------------------
         1 10.5-10.10
         2 2.1-2.10
         3 5.5-5.7
         4 4.3
         5 9.9, 8.1-8.3, 7.2-7.5, 2.6

5 rows selected.

SQL> col val  format a50
SQL> with 
  2    a as (
  3      select id, rn, 
  4             translate(substr(col, decode(rn,1,1,instr(col, ',', 1, rn-1)+1),
  5                              instr(col, ',', 1, rn)
  6                              -decode(rn,1,0,instr(col, ',', 1, rn-1))-1),
  7                       'a, ','a')
  8               val0
  9      from ( select id, col, 
 10                    row_number() over (partition by id order by rownum) rn
 11             from dual, (select id, col||',' col from t) t
 12             connect by prior id = id
 13                    and prior dbms_random.value is not null
 14                    and level <= 
 15                          length(t.col)-length(replace(t.col,',','')) )
 16    ), 
 17    b as (
 18      select id, rn rn0, 
 19             to_number(substr(val0,1,instr(val0,'.')-1)) valb,
 20             to_number(substr(val0,instr(val0,'.')+1,
 21                              decode(instr(val0,'-'),0,length(val0)+1,
 22                                                       instr(val0,'-'))
 23                              -instr(val0,'.')-1)) val1,
 24             to_number(substr(val0,
 25                            greatest(instr(val0,'.',1,2),instr(val0,'.',1,1))
 26                              +1)) val2
 27      from a
 28    )
 29  select id, max(substr(sys_connect_by_path(val,','),2)) val
 30  from ( select id, 
 31                to_char(valb)||'.'||to_char(val1+rn) val,
 32                row_number () over (partition by id order by rn0, rn) curr,
 33                row_number () over (partition by id order by rn0, rn)-1 prev
 34         from ( select id, rn0, valb, val1,
 35                       row_number () 
 36                         over (partition by id, rn0 order by rownum) - 1 rn
 37                from dual, b 
 38                connect by prior id = id and prior rn0 = rn0 
 39                       and prior dbms_random.value is not null
 40                       and level <= b.val2-b.val1+1 )
 41         )
 42  connect by prior curr = prev and prior id = id
 43  start with curr = 1
 44  group by id
 45  order by id
 46  /
        ID VAL
---------- -------------------------------------------------
         1 10.5,10.6,10.7,10.8,10.9,10.10
         2 2.1,2.2,2.3,2.4,2.5,2.6,2.7,2.8,2.9,2.10
         3 5.5,5.6,5.7
         4 4.3
         5 9.9,8.1,8.2,8.3,7.2,7.3,7.4,7.5,2.6

5 rows selected.

Yes, it is not maintainable but it is fun.

Regards
Michel 

Thanks

Yiming Li, March 02, 2006 - 3:01 pm UTC

Thanks, Michel, you are smart too.

yiming

Evaluate arithmetic stored in VARCHAR2

Ronald, August 07, 2006 - 7:28 am UTC

Hi Tom,
Could we evaluate an arithmetic expressions stored as string (varchar2) ?
That is the problem I faced today. I have formula (to calculate output needed by user)
that often changed (based on user experiment), so to be hardcoded in application is wrong thing, I think.
The idea, I create a table that store the formula so when user propose to change
they can do by self (using API I provide) without modifying the original code.

create table t ( expr varchar2(100) );

insert into t values ('(10-2*4.3)/2');
insert into t values ('13+(5.6-0.5*2.1)/1.2');
insert into t values ('6^2-3.1');

select * from t;

EXPR
--------------------
(10-2*4.3)/2-1
13+(5.6-0.5*2.1)/1.2
6^2-3.1

The result I expected from that table is arithmetic operation applied to
that value. In real condition I use some variables express by a letter (A..Z).
But here for simplified the question I remove that variable to all constant.
Result I am expected for the query is :

select evaluate(expr) from t;

EVALUATE(EXPR)
--------------------
0.7
16.7916666666667
32.9

Could you help me to write PL/SQL for the evalute function or you have another
better technique to solve the problem ?
The operator we used (separate by comma) is +,-,*,/,(,),^

Thanks,
Ronald

Many Thanks Tom

Ronald, August 07, 2006 - 11:24 am UTC

Great !!! Is what I expected.

A reader, March 02, 2008 - 6:25 am UTC

How to generate a series for alphanumeric strings?

drop table t purge;

create table t (name varchar2(30));

insert into t values('001-010');
insert into t values('002-100');
insert into t values('1-10');
insert into t values('2-100');
insert into t values('01A-10A');
insert into t values('A01-A10');
insert into t values('B1-B50');
insert into t values('01-100');
insert into t values('v-z');
insert into t values('G-L');

for '001-010', the series will be 001, 002, 003, 004, 005, .. 010.


Tom Kyte
March 02, 2008 - 10:46 am UTC

you write code

or you use a sequence and forget about having anything resembling "gap free"

A reader, March 03, 2008 - 11:22 pm UTC

Yes, i am trying to right the code. but the scenario here is the user will enter the range and the series has to be generated. But the user may enter in any format say, number followed by characters or all characters or all numbers or number followed by a char followed by a number.But everything gets stored as a character string. can it be done in a sequence? if so, please tell me how?
Tom Kyte
March 04, 2008 - 7:17 am UTC

you will write code.

just write a stored procedure - there is nothing automagical here, you will write code.

how to separae digits and letters

Ahmed, April 01, 2008 - 7:27 am UTC

hi Tom,
How are you?
In our database we have an old field for book pages, e.g 300p , meaning that the book has 300 pages, and some fdata like: {300 pages}, an so on.
The new build, I implement the table as tow fileds to pages of the book, One for numbers , and the other for types. eg. field1 = 300, and field2 = p.
And the users enter some about 1 million book in the old format, and want from me to separate the data automatically.
can you help me please.

Ahmed.
Tom Kyte
April 01, 2008 - 9:38 am UTC

ops$tkyte%ORA10GR2> select x,
  2         replace(translate(x,'0123456789','0'),'0','') not_digits,
  3         replace( translate( x, replace(translate(x,'0123456789','0'),'0',''), 'x'), 'x', '' ) digits
  4    from (select '300p' x from dual)
  5  /
X    NOT_ DIGI
---- ---- ----
300p p    300

1 row selected.



the digits function can be simplified if you know the domain of alpha characters you will hit - I did not know any character beyond "p" so I punted and used the data itself to find the characters involved.

Validating strings using Regular Expressions

John Gasch, April 01, 2008 - 3:50 pm UTC

Getting back to the root topic of this thread, I'm rather perplexed that nobody has suggested doing numeric string validation using regular expressions.

A solution to the original poster's problem would be:
select *
  from t
  where tmptype = 'N'
  and not regexp_like(tmpvalue,
              '^(+|-)?((\d+\.\d*$)|(\d+$)|(\d*.\d+$))' );


The regexp would match variations of numbers, such as:
  0
  0.
  0.0
  .0

each optionally preceded by one + or - sign character, with no embedded blanks or any other character. So the query will return all rows labeled 'N' that have a value that doesn't look like a number.

I find regular expressions to be an extremely powerful tool in my toolbox. They are documented here:
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm

Regular expressions could potentially be applicable to a number of the other problems encapsulated in this discussion thread.

Tom Kyte
April 01, 2008 - 9:11 pm UTC

, version 8.1.7

see the original subject - from years and years and many versions ago.


I myself do not like regular expressions, really. If there was a single definition of them - maybe - but there are way too many variants of them.



REGEXP

John Gasch, April 01, 2008 - 10:33 pm UTC

Fair enough. They can be cryptic. Hopefully by now their implementation has matured, at least in Oracle's built-in REGEXP functions.

When I started writing this review, your reply to my previous post included the observation that I hadn't considered all forms of numeric literals. (It appears now that you rescinded that remark, even though it was valid.) So here's a version that's more robust just for completeness...

select *
  from t
  where tmptype = 'N'
  and not regexp_like(tmpvalue,
      '^(\+|\-)?((\d+\.\d*)|(\d+)|(\.\d+))(E(\+|\-)?\d+)?$' );


(I admit that I neglected a few backslashes in the prior version.)


REGEXP

John Gasch, April 01, 2008 - 11:09 pm UTC

Oops, I needed a trailing 'i' in the REGEXP_LIKE arg list to make it case insensitive so it picks up numbers like '1e0'.

select *
  from t
  where tmptype = 'N'
  and not regexp_like(tmpvalue,
     '^(\+|\-)?((\d+\.\d*)|(\d+)|(\.\d+))(E(\+|\-)?\d+)?$','i');


Sorry. I think I'm finished now.

following up

lou, July 30, 2013 - 7:35 pm UTC

Tom,

how can I the pipelined above to do the following:

TABLE STRUCTURE

ID, CATEGORY ..... 6 columns ....

1 STUDENT, CLASS, BUILDING,PRINCIPAL



ID COLUMN_VALUE column3 column4 ...col6
---------- -------------------------
1 STUDENT
1 CLASS
1 BUILDING
1 PRINCIPAL

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library