Link to home
Start Free TrialLog in
Avatar of Gary Antonellis
Gary AntonellisFlag for United States of America

asked on

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
Avatar of Sean Stuber
Sean Stuber

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
case when regexp_instr(some_value,'^[0-9]+$') = 0 then some_value else null end
The regexp_substr above is better than my case statement.
Avatar of Walter Ritzel
Walter Ritzel
Flag of Brazil image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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;
Avatar of Gary Antonellis


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 DeveloperUser generated image
User generated image
>>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.
Worked perfectly, Thanks!
>>Worked perfectly, Thanks!

Even though it can return bad results as shown by sdstuber?
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
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.