All Forums Extensibility
toadrw 69 posts Joined 07/04
06 Dec 2011

Hello everybody:


In migrating from Oracle to Teradata 13, we are having difficulty finding a replacement for Oracle's REGEXP_LIKE() function.  It is our understanding that there might be a patch for this on Teradata 13.  If so, could you provide some more information on this?  If there is a UDF which might replace this function? That would also be helpful.


Thank you.

dnoeth 4628 posts Joined 11/04
07 Dec 2011

Those Oracle RegExp functions are announced for TD14, so you have to write your own UDF.



Jim Chapman 449 posts Joined 09/04
07 Dec 2011

Even so, I don't see REGEXP_LIKE in the list of implemented functions for 14.0.  We offer REGEXP_SIMILAR, which might be a satisfactory substitute.

gpolanch 46 posts Joined 12/11
08 Dec 2011

Hi Folks, I am a Teradata newbie, and in the same situation as "toadrw", migrating from Oracle 11g to Teradata 13.10, and need to find a solution for REGEXP.  Thanks to this site, I found my way to UDF's and the UDF Programming Manual.   I'm trying the Scalar example in Appendix B using SQL Assistant, and getting the following error:

Failed [5594 : HY000] Invalid external file option specified for UDF/XSP/UDM/UDT 'Find_Text'.

From what I understand, the EXTERNAL NAME is to point to the C source file, so I set it as below, (a folder on my hard drive) though the Manual has it as


When referencing EXTERNAL NAME in the manual, it mentions network-attached and channel-attached and the use of BTEQ. Must BTEQ be used for this operation?  Can someone tell me how the value for EXTERNAL NAME is to be constructed?  Thanks much!

CREATE FUNCTION Find_Text( Searched_String VARCHAR(500),Pattern VARCHAR(500) )





EXTERNAL NAME 'C:\try\pattern.c';



Jim Chapman 449 posts Joined 09/04
08 Dec 2011

The EXTERNAL NAME literal string should be 'CS!pattern!c:/try/pattern.c!F!Find_Text'.

The initial 'C' indicates that the source is on the client.

The 2nd character, 'S', introduces the source indentifier, where '!' is a required delimiter, 'pattern' is the name that will be used to store a copy of the file on the server, and 'c:/try/pattern.c' is your source path.

Then '!F!' introduces the entry point symbol to be bound to the SQL function name.

gpolanch 46 posts Joined 12/11
09 Dec 2011

Thanks much Jim.  That got me over a big hurdle, and my UDF now works when I run it from a select statement.  Also created my own version of the Oracle UPPER() function.

You must sign in to leave a comment.