Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Stripping numeric data in varchar(900)
From:   D silva, Joseph Vinish

  I have a field that contains varchar(900) that I would need to strip only the numeric data from. If there are multiple numbers separated by any text (including spaces) I would like to capture them as separate numbers.  

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"
     #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)

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

       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)
     NO SQL
     SPECIFIC extractNums
     EXTERNAL NAME 'CS!extractNums!D:\UDFGadgets\extractNums.c'

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023