All Forums UDA
teradatashrini 1 post Joined 11/06
27 Nov 2006
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"

VandeBergB 182 posts Joined 09/06
08 Dec 2006

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

Some drink from the fountain of knowledge, others just gargle.

amchoubey 4 posts Joined 08/11
21 Aug 2011

I want to re-write the query given below (I found this query in a forum Link -

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

rluebke 65 posts Joined 11/05
25 Aug 2011

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

amchoubey 4 posts Joined 08/11
26 Aug 2011

Thanks Rluebke for your comment. 

Do you know what could be alternative solution? 

- Anand


Jimm 298 posts Joined 09/07
30 Aug 2011

Get your DBA's to download and install the Oracle UDFs for Teradata
( )
Then you can use the otranslate function - just like Oracle (and many other Oracle funstions).

ogguz 3 posts Joined 09/11
14 Sep 2011

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.

dnoeth 4628 posts Joined 11/04
14 Sep 2011

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



You must sign in to leave a comment.