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"
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
There must be a happy medium somewhere between being totally informed and blissfully unaware...
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.
Teradata does not natively support regular expressions. That is why the '%[0-9.-]%' doesn't work.
Thanks Rluebke for your comment.
Do you know what could be alternative solution?
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).
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.
Contact your Teradata support, they got Unicode versions of the Oracle UDFs.