Hello Team -
I have one column which has Varchar(20) data length but I want to pass only those rows which have Numeric value only. I use To_Number function and check if the output is Not Null then pass else filter. But in the case of decimal value like 43764387.37, this will also pass.
I want if there is anything else 0-9 it should filter.
Your early response is very much appreciated.
addition to the previous post, I used regexp_instr(trim(x),'^[0-9]+$') = 1 in where clause but query performance is very poor.
Please suggest if this is not the correct way.
regexp have some CPU cost but usually this is not the main factor of a query.
What is the amount of data in the table and only numerics ?
Maybe you can try other solution like :
regexp_similar(x, '^[0-9]+$') = 1 otranslate(x, 'A0123456789', 'A') is null
Or maybe to_number is the way to go, replace first the dot by something like a dollar.
You want INTEGERs, no fractional part?
WHERE To_Number(x) MOD 1 = 0
removes all non-numeric and fractional numbers, but negative integers will pass.
@Waldar wrote:otranslate(x, 'A0123456789', 'A') is null
Are you sure?
Condition should be:
otranslate(x, 'A0123456789', 'A') = ''
And the other reflex is replacing an character with itself:
otranslate(x, 'A0123456789', 'A')
which is not needed because NULLs and empty strings are not equal :-)
otranslate(x, '0123456789', ''),