check for numeric values in a string

Database
zam
N/A

check for numeric values in a string

Is there any function in teradata to check for numeric value in a string?
One option is doing a substring to check for each field whether it falls in the numeric range.Any particular function avlbl in teradata?
8 REPLIES
Enthusiast

Re: check for numeric values in a string

I am trying to check to see if the string is a numeric or alpha-numeric. I have tried the following but it is not giving right results.
case when
cola like '%1%' or
cola like '%2%' or
cola like '%3%' or
cola like '%4%' or
cola like '%5%' or
cola like '%6%' or
cola like '%7%' or
cola like '%8%' or
cola like '%9%' or
cola like '%0%'
then 'number found'
else 'no number found'
end

Ex: If my string is 'abc1sur' then cola like '%1%' give me success but it is not really a numeric.

Any other suggestions?
Enthusiast

Re: check for numeric values in a string

probably you need to enclose the expressions in braces. Try using "LIKE (%1%)"
Enthusiast

Re: check for numeric values in a string

probably you need to enclose the expressions in braces. Try using "LIKE (%1%,%2%,.......)"
rgs
Enthusiast

Re: check for numeric values in a string

It seems like there should be an easier way. The only thing I could come up with besides using a UDF is the following example:

SELECT a, b,
CASE WHEN b LIKE ANY (digits ) THEN
CASE WHEN b LIKE ANY (non-digits ) THEN
'string'
ELSE 'number' END
ELSE 'string' END
FROM numchar ORDER BY a;
Where 'digits' and 'non-digits' is:

digits ::= '%0%','%1%','%2%','%3%','%4%','%5%','%6%','%7%','%8%','%9%'

non-digits ::= '%A%','%B%','%C%','%D%','%E%','%F%','%G%','%H%','%I%','%J%','%K%','%L%','%P%','%M%','%N%','%O%','%P%','%Q%','%R%','%S%','%T%','%U%','%V%','%W%','%X%','%Y%','%Z%'

Your 'non-digits' list needs to contain all characters that you could expect in the string other than the numeric digits. The one above only contains the alphabet. It does not need lower case since the comparison is not case specific unless you designate CASESPECIFIC in your expression.

So the first case checks to see if there are any digits in the string. If there are the inner case checks to make sure the string does not contain any non-digit characters. If there are no non-digit characters it returns ‘number’. If there is non-digit it returns ‘string’. If the outer case finds no digits then it returns ‘string’.

A test run (b is the original string):

select a, b, case when b like any ('%0%','%1%','%2%','%3%','%4%','%5%','%6%
','%7%','%8%','%9%' ) THEN
case when b like any ('%A%','%B%','%C%','%D%','%E%','%F%','
%G%','%H%','%I%','%J%','%K%','%L%','%P%','%M%','%N%','%O%','%P%','%Q%','%R
%','%S%','%T%','%U%','%V%','%W%','%X%','%Y%','%Z%') THEN
'string'
else 'number' end
else 'string' end
from numchar order by a;

*** Query completed. 5 rows found. 3 columns returned.
*** Total elapsed time was 1 second.

a b CASE expression
----------- ---------- ------------------
1 ab1cdlist2 string
2 not number string
3 24689 number
4 23459 number
5 23459a string

Re: check for numeric values in a string



This solution semms good, but it needs also special character ("$","/"...) in non-digits list.
Can anyone post a list of special characters?

Re: check for numeric values in a string

Hi,

you can try this to check the value is an numeric or not......

CASE
WHEN
UPPER (TRIM (value to check is_numeric)(CASESPECIFIC ) = LOWER (TRIM (value to check is_numeric) )(CASESPECIFIC)
THEN 'NUMERIC'
ELSE 'NON_NUMERIC'
END
Junior Contributor

Re: check for numeric values in a string

Hi Nanda,
this only works for characters 'a'..'z', but fails for anything else,
e.g. '"§$%&/('

The only reliable way is a UDF.

Dieter
Junior Supporter

Re: check for numeric values in a string

>> The only reliable way is a UDF.

Not necessarily (well, yes: oTranslate(), but not one UDF 'ad-hoc').

If you are looking for NUMBERS AND ONLY NUMBERS (no dots, signs, etc...) in a string you may take a look at this solution I provided for a colleague:

http://carlosal.wordpress.com/2009/12/02/only-numbers/

HTH.

Cheers.

Carlos.