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%
For example, the following will return '3' when user enters '3'
select DECODE( TRANSLATE('3','0123456789'
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
The following sql works correctly if the INPUT_STRING is only one digit
select DECODE( TRANSLATE('[%INPUT_STRING%
For example, the following will return '3' when user enters '3'
select DECODE( TRANSLATE('3','0123456789'
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
Last Comment
Walter Ritzel
try:
case when regexp_instr(some_value,'^
case when regexp_instr(some_value,'^
The regexp_substr above is better than my case statement.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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,'a0
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,'a0
Note,
LENGTH(TRIM(TRANSLATE('123
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
select LENGTH(TRIM(TRANSLATE('1-.
LENGTH(TRIM(TRANSLATE('123
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
select LENGTH(TRIM(TRANSLATE('1-.
ASKER
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 Developer![Capture.JPG]()
![Capture.JPG]()
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 Developer
>>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,'^
replace some_value with the correct column and some_table with the correct table.
That said: I like: select regexp_substr(input_string
replace input_string and dual wioth the correct column and table.
You'll need the SELET around it to make it a valid statement.
select case when regexp_instr(some_value,'^
replace some_value with the correct column and some_table with the correct table.
That said: I like: select regexp_substr(input_string
replace input_string and dual wioth the correct column and table.
ASKER
Worked perfectly, Thanks!
>>Worked perfectly, Thanks!
Even though it can return bad results as shown by sdstuber?
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
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
@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.
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.
select input_string from dual
where regexp_like(input_string,'
return input_string if integer or NULL if not
select regexp_substr(input_string