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

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

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? 

- Anand

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.

Dieter