Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 25 Apr 2008 @ 13:30:56 GMT

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

Subj:   Re: Extract values from field
From:   Stieger, Etienne E

Craig Ferry wrote:

  I need to write an SQL on a text field. Basically the text field could contain any number of characters. I need to extract only the numeric. The field is VARCHAR(2000).  

Hi Craig,

A solution you could use would involve 2 Oracle UDF's (otranslate and oreplace) from Teradata web site. I have attached the C code for your convenience.

Assuming the column you are trying to remove non-numeric data from has the value '0ABCD1234EFGH56789' (you can substitute this with your varchar(2000) column name):

     CDEFGHIJKLMNOPQRSRUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@')||
     ABCDEFGHIJKLMNOPQRSRUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@')||
     'ABCDEFGHIJKLMNOPQRSRUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@')||
     'ABCDEFGHIJKLMNOPQRSRUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@');

Depending on what non-numeric characters might be present in the input data, you might need to extend the following two translation strings:


(I haven't tested, but you might need to also cater for lower case)

The reason we are concatenating 4 times, is because these functions only handle up to 512 characters, and you need to handle up to 2000.

Kind regards

Etienne Stieger
Analytics and Information Management (AIM/EIW)
Standard Bank of South Africa Ltd

Admin Comment: Due to a DMCA Infringement Notice from Teradata, we have removed this attachment.

  <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: 28 Jun 2020