How to validate telephone number in sql

[DFW1999]

There is a table one column:
telephone_number
1234567777
1112223333
2223334444
%#@&008988
+dhjkky854
78-- 00=7

How in sql you can select only valid telephone numbers(10 numeric character long). In this case I want to see in my sql output only the following output:
1234567777
1112223333
2223334444

I do not want INVALID telephone numbers.
%#@&008988
+dhjkky854
78-- 00=7
Is there any built in function in Teradata to check that?

Thanks

 

[BillDHS]

I have seen suggestions using:

WHERE UPPER(colA) = LOWER(colA), but this may only work in ASCII mode because Teradata mode doesn't care about case, and if there is a letter in the field because UPPER will not = LOWER.

Otherwise a big OR clause may be necessary

 

[tdatgod]

if you are running 5.1 you can write a UDF called IsInteger to validate it contains only numbers.


Something like this....

#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;
}

 

[CapsuleCorpJX]

Are regular expressions supported Teradata SQL?

 

[tdatgod]

Hi,
I know someone was in the process of writing a regular expression UDF but I can't seem to find it anywhere.

 

[CapsuleCorpJX]

Is NCR considering adding Regular Expressions as a functionality in future releases?

UDF's are fine, but I'd rather not use anything from third parties in querying Teradata.

I think it would definately be helpful.

 

[rkumar28]

Just incase if this is still an issue. Look into this link and modify the query as per your need.

http://www.teradata.com/teradataForum/shwmessage.aspx?forumid=1&messageid=1530#bm1539

Thanks
Raj