REGEXP_LIKE

Extensibility
Enthusiast

REGEXP_LIKE

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.

5 REPLIES
Junior Contributor

Re: REGEXP_LIKE

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

Dieter

Enthusiast

Re: REGEXP_LIKE

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.

Enthusiast

Re: REGEXP_LIKE

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

'CS!pattern!dbs_functions/pattern.c!F!Find_Text'

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) )


RETURNS CHAR


LANGUAGE C


NO SQL


PARAMETER STYLE TD_GENERAL

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

 

Enthusiast

Re: REGEXP_LIKE

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.

Enthusiast

Re: REGEXP_LIKE

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.