eliminating non-integer characters

Tools
N/A

eliminating non-integer characters

Hello all,
I am using Fast Export tool to extract data from Teradata to our Unix flat files. I have a requirement where I should filter all non-digit characters from the given field. For example if the data field contains AB123-45-6789MM, I should export that data as 123456789 to my Unix file.

Any help is highly appreciated,
Thank you,
Nishi

1 REPLY

Re: eliminating non-integer characters

You can either use unix scripting to do that, use an outmod, or use a UDF like this ......

/* C program source for UDF, store it under C:\myUDFS\extractdigits.c */
#define SQL_TEXT Latin_Text
#include "sqltypes_td.h"
#include
#define ISNULL -1
#define ISNOTNULL 0
#define NOSQLERROR "00000"

void extractdigits
(
VARCHAR_LATIN *inputStr
,VARCHAR_LATIN *outputStr
,int *inputStrIsNull
,int *outputStrIsNull
,char sqlstate[6]
,SQL_TEXT extname[129]
,SQL_TEXT specific_name[129]
,SQL_TEXT error_message[257]
)
{
if ((*outputStrIsNull = *inputStrIsNull) == ISNULL)
return;

while (*inputStr != '\0')
{
if (*inputStr >= '0' && *inputStr <= '9') *outputStr++ = *inputStr;
inputStr++;
}
*outputStr = '\0';

strcpy(sqlstate, NOSQLERROR);
strcpy((char *) error_message, " ");
}

/* End of C program */

-- run it from bteq to install the UDF
REPLACE FUNCTION extractdigits
(
str VARCHAR(255)
)RETURNS VARCHAR(255)
LANGUAGE C
NO SQL
SPECIFIC extractdigits
EXTERNAL NAME 'CS!extractdigits!C:\myUDFS\extractdigits.c'
PARAMETER STYLE SQL;

and in your fast export use the udf ......

SELECT COL1, COL2, extractdigits(COL3), ......

Once you have tested to make sure that the UDF is doing what you want, alter the function to make it run on unprotected mode.