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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
slightwv (䄆 Netminder) Commented:
The regexp_substr above is better than my case statement.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.