topic Re: Usage of Oracle Functions in Talend(SUBSTR and INSTR) in Talend Studio

Usage of Oracle Functions in Talend(SUBSTR and INSTR)

Anonymous — Sat, 16 Nov 2024 14:06:35 GMT

Hi,

I want to use the below expression in Talend 7.2 ESB Version in Toracle component.. It works fine in oracle. But throws error in Talend. Can you please help

replace(REGEXP_SUBSTR(ALT.alt_to_name,'[^,]+',1,2),'"','')as Lastname

Re: Usage of Oracle Functions in Talend(SUBSTR and INSTR)

Anonymous — Mon, 05 Jan 2009 17:18:50 GMT

Hi,
Are you using your SUBSTR oracle function into a tOracleInput component or into another one ?
Are you using a Java or Perl project?
You have different ways to resolve it.
You can use directly Oracle function into the SQL expression (tOracleInput); or write in Java or Perl in the tMap component.
The better way is to write into a tMap this transformation; of course if one day you modify your source (oracle) to another source (IBM DB2); your job will still working.
Best regards;

Re: Usage of Oracle Functions in Talend(SUBSTR and INSTR)

Anonymous — Tue, 06 Jan 2009 02:39:56 GMT

Hi Cantoine
Thank you for the reply I am using the Java Project. I need to do this transformation in the tMap but i coundnt find the functions which will solve the purpose of SUBSTR and INSTR.
Is there any function in Talend will do this or i need to write a user defined function in Java which will solve the purpose??

Thanks
Selva.

Re: Usage of Oracle Functions in Talend(SUBSTR and INSTR)

Anonymous — Tue, 06 Jan 2009 21:03:23 GMT

Hi Selva,
you could use the functions of the String object in java. For example:

rowN.variable.substring(beginIndex, endIndex)

Because I don't know the INST function I couldn't give you a java equivalent.
You could find more information in the JDK documentation.
Bye
Volker

Re: Usage of Oracle Functions in Talend(SUBSTR and INSTR)

Anonymous — Wed, 07 Jan 2009 11:10:42 GMT

Hi Selva,
To replace INSTR (oracle expression) to a Java expression your can use the following syntax : rowN.variable.indexOf(':')
indexOf(':') => return the position where character : is present.

Oracle INSTR(PROP25,':',1,1) and Java rowN.variable.indexOf(':') are equal.

SUBSTR(PROP25,INSTR(PROP25,':',1,1)+1) => you can write this : rowN.variable.substring((rowN.variable.indexOf(':'))+1)

Best regards

Re: Usage of Oracle Functions in Talend(SUBSTR and INSTR)

Divya3 — Sat, 25 Jul 2020 10:48:56 GMT

Hi,

I want to use the below expression in Talend, Can you please help

replace(REGEXP_SUBSTR(ALT.alt_to_name,'[^,]+',1,2),'"','')as Lastname