How to Pass only Numeric Value

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything

How to Pass only Numeric Value

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.

6 REPLIES

Re: How to Pass only Numeric Value

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.

Teradata Employee

Re: How to Pass only Numeric Value

Hi ssss2018T,

 

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.

 

 

 

Highlighted
Junior Contributor

Re: How to Pass only Numeric Value

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.

 

 

Re: How to Pass only Numeric Value


@Waldar wrote:

otranslate(x, 'A0123456789', 'A') is null

Are you sure?

Condition should be:

otranslate(x, 'A0123456789', 'A') = ''

 

Teradata Employee

Re: How to Pass only Numeric Value

Indeed, old reflex from the red database where nulls and empty strings are the same.

Junior Contributor

Re: How to Pass only Numeric Value

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', ''),