Identify if string is integer using Oracle SQL

I have to write an sql command that will return null if a string is not an integer, return the string contents if it is an integer.  This sql command will be embedded in an application and cannot reference a custom function.

The following sql works correctly if the INPUT_STRING is only one digit
    select DECODE( TRANSLATE('[%INPUT_STRING%]','0123456789',' '), NULL, '[%INPUT_STRING%]',' ') from dual

For example, the following will return '3' when user enters '3'
   select DECODE( TRANSLATE('3','0123456789',' '), NULL, '3',' ') from dual

But if user enters '31', it will fail.

Can you provide an SQL that will return the contents of INPUT_STRING when it contains only the digits 0123456789.  

A second best alternative would be returning Null if INPUT_STRING contains a decimal point, otherwise return contents of INPUT_STRING
Gary AntonellisSenior Application ConsultantAsked:
Who is Participating?
 
Walter RitzelSenior Software EngineerCommented:
The function below will return null in case of valid numeric and a number otherwise:
LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' ')))

Open in new window


So you can use like this:
select DECODE(LENGTH(TRIM(TRANSLATE('3', ' +-.0123456789',' '))), NULL, '3',' ') from dual;
select DECODE(LENGTH(TRIM(TRANSLATE('31', ' +-.0123456789',' '))), NULL, '31',' ') from dual;
select DECODE(LENGTH(TRIM(TRANSLATE('31232', ' +-.0123456789',' '))), NULL, '31232',' ') from dual;
select DECODE(LENGTH(TRIM(TRANSLATE('3b', ' +-.0123456789',' '))), NULL, '3','3b') from dual;

Open in new window

0
 
sdstuberCommented:
only return value when input_string is an integer

select input_string from dual
where regexp_like(input_string,'^[0-9]+$')

return input_string if integer or NULL if not

select regexp_substr(input_string,'^[0-9]+$') from dual
0
 
slightwv (䄆 Netminder) Commented:
try:
case when regexp_instr(some_value,'^[0-9]+$') = 0 then some_value else null end
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
The regexp_substr above is better than my case statement.
0
 
sdstuberCommented:
If you don"t want to use regular expressions you can use translate which might be more efficient


this will return input_string if it is an integer and return no rows if it is not

 select input_string from dual
 where translate(input_string,'a0123456789','a') is null
0
 
sdstuberCommented:
Note,

LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' ')))

is not reliable for determining if a given string is a number or not because it can't determine if there are duplicate non-digit values

for example, both of these return NULL which is "supposed" to indicate numeric, but they aren't

select LENGTH(TRIM(TRANSLATE('123.................................567', ' +-.0123456789',' '))) from dual;
select LENGTH(TRIM(TRANSLATE('1-.-2.-.3----4....5', ' +-.0123456789',' '))) from dual;
0
 
Gary AntonellisSenior Application ConsultantAuthor Commented:
I am showing my limited SQL knowledge....

In the first example from sdstuber is that a single SQL statement?  I can only enter a single SQL statement in this application.

In the second example I am getting error 'Error report - Unknown Command' in SQL DeveloperCapture.JPG
Capture.JPG
0
 
slightwv (䄆 Netminder) Commented:
>>In the second example I am getting error 'Error report - Unknown Command' in SQL Developer

You'll need the SELET around it to make it a valid statement.

select case when regexp_instr(some_value,'^[0-9]+$') = 0 then some_value else null end from some_table;

replace some_value with the correct column and some_table with the correct table.

That said:  I like:  select regexp_substr(input_string,'^[0-9]+$') from dual

replace input_string and dual wioth the correct column and table.
0
 
Gary AntonellisSenior Application ConsultantAuthor Commented:
Worked perfectly, Thanks!
0
 
slightwv (䄆 Netminder) Commented:
>>Worked perfectly, Thanks!

Even though it can return bad results as shown by sdstuber?
0
 
sdstuberCommented:
Yes -

 seems odd that the one answer that has been shown to produce wrong results is the one that was accepted when the others work fine.

or, if not, haven't been shown not to work
0
 
Walter RitzelSenior Software EngineerCommented:
@gantone1,
although my answer solve your problem, I need to agree with the other experts that is not the best one, as it will fail in corner cases (like the ones listed by @sdstuber).

I would advise you to test all solutions and give the points to the one that works as you need and not present any flaws.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.