Avatar of Gary Antonellis
Gary Antonellis
Flag 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
Oracle DatabaseSQL

Avatar of undefined
Last Comment
Walter Ritzel

8/22/2022 - Mon
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
slightwv (䄆 Netminder)

case when regexp_instr(some_value,'^[0-9]+$') = 0 then some_value else null end
slightwv (䄆 Netminder)

The regexp_substr above is better than my case statement.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Walter Ritzel

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Sean Stuber

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


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;
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 DeveloperCapture.JPG
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

>>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.
Gary Antonellis

Worked perfectly, Thanks!
slightwv (䄆 Netminder)

>>Worked perfectly, Thanks!

Even though it can return bad results as shown by sdstuber?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Sean Stuber

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
Walter Ritzel

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.