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):

     select
     sysdba.oreplace(sysdba.otranslate(substr('0ABCD1234EFGH56789',1,500),'AB
     CDEFGHIJKLMNOPQRSRUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@')||
     sysdba.oreplace(sysdba.otranslate(substr('0ABCD1234EFGH56789',501,500),'
     ABCDEFGHIJKLMNOPQRSRUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@')||
     sysdba.oreplace(sysdba.otranslate(substr('0ABCD1234EFGH56789',1001,500),
     'ABCDEFGHIJKLMNOPQRSRUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@')||
     sysdba.oreplace(sysdba.otranslate(substr('0ABCD1234EFGH56789',1501,500),
     'ABCDEFGHIJKLMNOPQRSRUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@');

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

     'ABCDEFGHIJKLMNOPQRSRUVWXYZ'
     '@@@@@@@@@@@@@@@@@@@@@@@@@@'

(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: The attachments can be found at:

www.teradataforum.com/attach.htm



     
  <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