How to remove non-numeric characters from a string? (Oracle TRANSLATE Equivlent function?)

UDA

How to remove non-numeric characters from a string? (Oracle TRANSLATE Equivlent function?)

How to remove non-numeric characters (Spaces, brackets, alpha....) from a string? I am looking for a function (or a way) in Teradata similar to Oracle Translate function?

Example:

StrInput = "(800)555-1212 "
StrOutput = "8005551212"

StrInput = "1 800 555 1212"
StrOutput = "18005551212"
7 REPLIES
Enthusiast

Re: How to remove non-numeric characters from a string? (Oracle TRANSLATE Equivlent function?)

select
substring(strInput from (position('(' in strinput)+1 for 3) -- returns area code
||
substring(strinput from position(strinput(')' in strinput +1 for 3) --returns line prefix
||
substring(strinput from position('-' in strinput)+1 for 4) -- returns line number

Re: How to remove non-numeric characters from a string? (Oracle TRANSLATE Equivlent function?)

I want to re-write the query given below (I found this query in a forum Link - http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server):

Select Left(SubString(Data, PatIndex('%[0-9.-]%', Data), 8000), PatIndex('%[^0-9.-]%', SubString(Data, PatIndex('%[0-9.-]%', Data), 8000) + 'X')-1)

but in Teradata syntax. Some part the about query does not work the way it should in Teradata. For example, '%[0-9.-]%' is not working in Teradata.
please help
Enthusiast

Re: How to remove non-numeric characters from a string? (Oracle TRANSLATE Equivlent function?)

Teradata does not natively support regular expressions. That is why the '%[0-9.-]%' doesn't work.

Re: How to remove non-numeric characters from a string? (Oracle TRANSLATE Equivlent function?)

Thanks Rluebke for your comment. 

Do you know what could be alternative solution? 

- Anand

Enthusiast

Re: How to remove non-numeric characters from a string? (Oracle TRANSLATE Equivlent function?)

Get your DBA's to download and install the Oracle UDFs for Teradata
( http://downloads.teradata.com/download/extensibility/teradata-udfs-for-popular-oracle-functions )
Then you can use the otranslate function - just like Oracle (and many other Oracle funstions).
Fan

Re: How to remove non-numeric characters from a string? (Oracle TRANSLATE Equivlent function?)

When I use the Oracle UDF's for Teradata in a Unicode system then teradata return an error.

Because UDFs are for the latin character set.

Any possibility to get UDFs for the unicode character set. I tried to do some changes on UDFs,

but I couldn't complete the functions.

Junior Contributor

Re: How to remove non-numeric characters from a string? (Oracle TRANSLATE Equivlent function?)

Contact your Teradata support, they got Unicode versions of the Oracle UDFs.

Dieter