Archives of the TeradataForum

Message Posted: Tue, 28 Aug 2007 @ 09:28:43 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

What you need is a UDF, there's a pure SQL solution with Recursion, but given the length of VARCHAR(900), I wouldn''t want to go anywhere near that.

Of course if you are receiving the data as such and would like to "cleanse" it before loading, there are different ways. (unix guys normally prefer to do a little bit of sed N awk stuff before loading, then there are the inmods which you can use with load utilities etc ....). You need to look into all the options and evaluate against your requirements before you choose which way you go.

I am attaching a tiny UDF I cooked up quickly ... ( You will need a compiler installation in one of your PE nodes to use UDFs though).... Remember to make it unprotected once you have tested it thoroughly.

     /* File D:\UDFGadgets\extractNums.c Use this in the SQL definition below*/
     #define SQL_TEXT Latin_Text
     #include "sqltypes_td.h"
     #include 
     #define ISNULL -1
     #define ISNOTNULL 0
     #define NOSQLERROR "00000"

     void extractNums
     (
       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]
     )
     {
       VARCHAR_LATIN *outputStrStart;

       if ((*outputStrIsNull = *inputStrIsNull) == ISNULL)
         return;

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

       if (outputStr != outputStrStart && *(outputStr-1) == ' ') *(outputStr-1) = '\0';
       else *outputStr = '\0';

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

     /* End of C program */


     -- SQL to install the UDF ... Remember to update the path to the C file.

     REPLACE FUNCTION extractNums
     (
       str VARCHAR(900)
     )RETURNS VARCHAR(900)
     LANGUAGE C
     NO SQL
     SPECIFIC extractNums
     EXTERNAL NAME 'CS!extractNums!D:\UDFGadgets\extractNums.c'
     PARAMETER STYLE SQL;

	<Prev	Next>		<<First	<Prev	Next>	Last>>