All Forums Database
zam 1 post Joined 12/04
08 Dec 2004
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?

sureshbadam 14 posts Joined 09/07
25 Oct 2007

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%' orcola like '%2%' orcola like '%3%' orcola like '%4%' orcola like '%5%' orcola like '%6%' orcola like '%7%' orcola like '%8%' orcola like '%9%' orcola 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?

leo.issac 184 posts Joined 07/06
25 Oct 2007

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

leo.issac 184 posts Joined 07/06
25 Oct 2007

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

rgs 106 posts Joined 02/07
26 Oct 2007

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

stefamagni 2 posts Joined 06/09
09 Jun 2009

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

nandaks04 1 post Joined 11/09
03 Feb 2010

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

dnoeth 4628 posts Joined 11/04
03 Feb 2010

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

Dieter

CarlosAL 512 posts Joined 04/08
05 Feb 2010

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

You must sign in to leave a comment.