Teradata IsNumeric

Does anyone know a way to filter out non-numeric characters in a char field?

I am looking for something like the following that works with Teradata:
Select * from table where IsNumeric(field1) = 1

I have a problem converting a string to an integer on a field that "should" contain all numbers, but it doesn't. Or if there's any way to not get a blasted "Bad Character in format or data" error whenever I try to convert the field to an integer.
JeepGeekinAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rockiroadsConnect With a Mentor Commented:
Have u considered creating a UDF and using that?
here is a premade one - look for is_integer.txt (2nd last post on page)
http://www.teradata.com/teradataForum/shwmessage.aspx?ForumID=5&MessageID=4532

the code for this (in C) is

/*-------------------------------------------------
-- Function is_integer
--Call exampel: Select:select mydatabase.is_integer('123A4677')
--Result  : 1 when Integer
--          0 when not Integer
--
--SQL-registration in Teradata:

REPLACE FUNCTION mydatabase.IS_INTEGER (myval VARCHAR(64000) CHARACTER SET LATIN)
 RETURNS INTEGER
 SPECIFIC is_integer_v
 LANGUAGE C
 NO SQL
 PARAMETER STYLE SQL
 DETERMINISTIC
 CALLED ON NULL INPUT
 EXTERNAL NAME 'SS!is_integer_v!/home/mydatabase/udf/is_integer_v.c'
;

--Compilation in Unix: cc -c is_integer_v.c -o is_integer_v.o
---

--------------------------------------------------------------
*/

#define SQL_TEXT Latin_Text
#include "sqltypes_td.h"
#include <string.h>
#include <stdlib.h>
#define IsNull -1
#define IsNotNull 0

void is_integer_v( VARCHAR_LATIN *inputString,
                   int *result,
                   int *inputStringIsNull,
                   int *ResultIsNull,
                   char sqlstate[6],
                   SQL_TEXT extname[129],
                   SQL_TEXT specific_name[129],
                   SQL_TEXT error_message[257] )
{
    char *ch;

    *error_message = '\0';
    *ResultIsNull = 0;

    /* make false result the default */
    *result = 0;

    /* Return Null value on Null Input */
    if ((*inputStringIsNull) == IsNull)
    {
        return;
    }

/* what is an Integer

         [+|-] xxxx
*/


    ch = (char *)inputString;

    /* Skip leading spaces */
    /*   remove this while line to disallow Leading whitespace */
    while ( isspace(*ch))   ch ++;

    if ( (*ch == '-') || (*ch == '+' ))
    {
        ch ++;
    }

        /* is the first character at least a digit? */
    if ( !isdigit(*ch) ) return ;

    /* skip over all the digits */
    /* already validated first digit above */
    do
    {
        ch++;
    }
    while (isdigit(*ch)) ;

    /* skip over the trailing white space */
    /* Remove this while line out   to disallow Trailing whitespace */
    while ( isspace(*ch))   ch ++;

    /* we should be at the end of the string.
       if not it is not an integer
    */
    *result = ( *ch == '\0' );

    return;
}





the other method described is to use UPPER

Select * from table where field1 <> upper(field1)

0
 
JeepGeekinAuthor Commented:
Thanks! I don't have the ability to add UDFs to this system, but the Upper approach worked for all characters but the "~", "`", "@","#","$", ...
Then I just filter thi smuch smaler list out. Also, I had to add a lower statement & a casespecific statement:

select * from Upper(field) <> Lower(Field) (CASESPECIFIC)

Thanks again!

0
 
rockiroadsCommented:
Cool and thanks for posting your solution, be very useful for others in the same predicament

:)

Best of luck with the rest of your project
0
 
JeepGeekinAuthor Commented:
OK. I feel stupid, so I'll post this as well. The Between statement can still recognize numeriic ranges when dealing with characters. This is probably the best way to handle this:

Select * from table where trim(field) BETWEEN '0' AND '99999'

(use as many 9's as you need)


0
All Courses

From novice to tech pro — start learning today.