Usage of Oracle Functions in Talend(SUBSTR and INSTR)

[Anonymous]

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 

[Anonymous]

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;

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Divya3]

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