Check field for numeric during Select

[cmmrfrds]

I am trying to retrieve records where a varchar field has a number. I want to make a select list of numbers to retrieve.

Of course, I started with the list of numbers but keep getting the error "invalid number" at runtime.

I got rid of the list and tried to check a simple number, but still get the error with this code.

AND (to_number(nvl(PAT_ENC_ES_AUD_ACT.ES_AUDIT_USER_ID,0)) > 9)

I only want the records that have numbers that will be in my select list. In SQL Server, I could use the IsNumeric function. Is there something similiar in Oracle?

 

[eagertotry]

I would use

length(translate(mystr,'0123456789A','A'))

If the string "mystr" contains only numeric characters, the length returned above would be null. If it contains at least one non-numeric character, it would return a non-null length. You may use nvl on top of this or simply check if length is null.

Hope this helps.

 

[sem]

Formatted numbers may contain both fractional and triad delimiters. Depending on NLS_NUMBER_FORMAT value fractional part may be either comma (,) or dot (.), while triads may or may not be separated. Both delimiters may be explicitly set by NLS_NUMERIC_CHARACTERS.

The most correct way to handle it is creating a small functionn e.g.

function isNumber(p in varchar2)
return boolean
is
test number;
begin
test := to_number(p);
return true;
exception
when VALUE_ERROR then return false;
end;

Of course you may choose return type at your will.

Regards, Dima

http://seminihin.narod.ru

 

[nagornyi]

Looks like what eagertotry suggested should be

length(translate(mystr,'A0123456789','A'))

 

[SantaMufasa]

I recommend and prefer using the strategy that Sem (Dima) proposes. For use outside of PL/SQL (i.e, for use in SQL statements, as well), I propose a slight adjustment to the code. Currently, if you use the "isNumber" function as written, the following error occurs in SQL venues:

select 'Num' from dual where isnumber('2345');
                                            *
ERROR at line 1:
ORA-00920: invalid relational operator

Here is a code adjustment that allows SQL usage:

Section 1 -- "isNumber" function definition:

create or replace function isNumber(p in varchar2)
return varchar2
is
  test number;
begin
  test := to_number(p);
  return 'TRUE';
exception
  when VALUE_ERROR then return 'FALSE';
end;

function created.

Section 2 -- Script of sample invocation of "isNumber" function:

accept val prompt "Enter a value to test as numeric: "
select '"&val" is '
    ||decode(isNumber('&val')
             ,'TRUE','numeric'
             ,'FALSE','not numeric')
  from dual;

Section 3 -- Script invocations and results:

SQL> @tt_234
Enter a value to test as numeric: 12345.67

"12345.67" is numeric

SQL> @tt_234
Enter a value to test as numeric: 12345.67.55

"12345.67.55" is not numeric

@tt_234
Enter a value to test as numeric: 12345a

"12345a" is not numeric

Let us know if this is a satisfactory resolution.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

In fact I personally prefer using getNumber rather than isNumber.

function getNumber(p in varchar2)
return number
is
  test number;
begin
  test := to_number(p);
  return test;
exception
  when VALUE_ERROR then return null;
end;

Thus I may both check the variable (getNumber is not null) and use its numeric value.


Regards, Dima

http://seminihin.narod.ru

 

[cmmrfrds]

Thank you everyone for your responses. I believe I misled you into thinking the need was to validate numeric. Instead, it was for the Record field in the supplied select list having invalid numeric data. I don't care what the value is if it doesn't match the select list, but I don't want the query to have a runtime error.

Here is what I meant by select list, perhaps I should have said IN list.
PAT_ENC_ES_AUD_ACT.ES_AUDIT_USER_ID IN (1234, 3435, 6768)

My concern was that PAT_ENC_ES_AUD_ACT.ES_AUDIT_USER_ID did not always have valid number User ID's - this is a user id in the record. For example, a user id in the Record might have 'T234' instead of '1234'.

Will this work.
(Cast PAT_ENC_ES_AUD_ACT.ES_AUDIT_USER_ID as Char(5)) IN
('1234', '3435', '6768')
Or don't I need to worry about a runtime error if the data is not a number.

 

[SantaMufasa]

...don't I need to worry about a runtime error if the data is not a number?

If you have defined PAT_ENC_ES_AUD_ACT.ES_AUDIT_USER_ID as "NUMBER" or "NUMBER(nn)", then no, you needn't worry...Oracle will not allow non-numeric data to reside in a NUMBER column.

So, is "PAT_ENC_ES_AUD_ACT.ES_AUDIT_USER_ID" defined as NUMBER?


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[cmmrfrds]

No, "PAT_ENC_ES_AUD_ACT.ES_AUDIT_USER_ID" is a varchar(255).

On the face, it does not make sense that I would try to have a number list for a varchar, but "PAT_ENC_ES_AUD_ACT.ES_AUDIT_USER_ID" is one user id field in a union query. The other 2 are numeric. For example. In the union query the list is in 3 where clauses.

The first union. varchar.
PAT_ENC_ES_AUD_ACT.ES_AUDIT_USER_ID IN
('1234', '3435', '6768')
The second union. numeric.
COVERAGE.VERIFY_USER_ID IN
(1234, 3435, 6768)
The third union. numeric.
PATIENT.REG_USER_ID IN
(1234, 3435, 6768)

The hardcoded list is really a single parameter of about 25 ids in a Crystal Report. So, in my Crystal Command it looks like.
The first union. varchar.
PAT_ENC_ES_AUD_ACT.ES_AUDIT_USER_ID IN
(?userIDs)
The second union. numeric.
COVERAGE.VERIFY_USER_ID IN
(?userIDs)
The third union. numeric.
PATIENT.REG_USER_ID IN
(?userIDs)

When Crystal generates the query it resolves the parameter to a list of id's. I don't want to maintain both a numeric and string parameter list since the id's change and there is more room for error. So, I was looking for a generalized way to force one way or the other.

 

[SantaMufasa]

Cmmrfrds,

Should all the entries in "PAT_ENC_ES_AUD_ACT.ES_AUDIT_USER_ID" (varchar(255)) be numeric? Does it disobey your business and processing rules to have an alphanumeric value in that column? If the individual values in that column should be numeric, then it seems to me that you can solve your problem once and for all by changing the column definition from "varchar(255)" to "NUMERIC", right?

If I am mistaken about all values needing to be numeric, then you should use a function similar to one above to test for numerics prior to each use.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[cmmrfrds]

Thank you for your time and advice. I will look at making all 3 fields the same data type.

 

[SantaMufasa]

Cmmrfrds,

Making all three columns the same datatype is certainly the best plan. To implement that "fix", you can do the following:

ALTER TABLE pat_enc_es_aud_act
    ADD audid_hold number;
UPDATE PAT_ENC_ES_AUD_ACT
    SET audid_hold = es_audit_user_id;
UPDATE PAT_ENC_ES_AUD_ACT
    SET es_audit_user_id = NULL;
ALTER TABLE pat_enc_es_aud_act
    MODIFY es_audit_user_id NUMBER;
UPDATE PAT_ENC_ES_AUD_ACT
    SET es_audit_user_id = audid_hold;
ALTER TABLE pat_enc_es_aud_act
    DROP COLUMN audid_hold;

Let us know of your success.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.