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

Attachments

Library

Quick Reference