Usage of Oracle Functions in Talend(SUBSTR and INSTR)

One Star

Usage of Oracle Functions in Talend(SUBSTR and INSTR)

Hi Guys,
I am using Talend with java version, I need to writean expression equivalent to Oracle's SUBSTR and INSTR functions.

My requirement is to convert this small oracle transformation by Talend's inbuilt functions
SUBSTR(PROP25,INSTR(PROP25,':',1,1)+1)
SUBSTR(PROP23,1,10)
Please suggest what function to use in talend to convert this.
I have already tried using the LEFT and INDEX functions for this but it dint work out because the LEFT function gives only the string upto to value specfied for position unlike the Oracle SUBSTR function gives the entire value from the position SUBSTR('testing',1) retuns back the entire string while Talend's LEFT func will return back only 't'. Please suggest me if there any alternative function can be used for SUBSTR and INSTR or any alternative solution for this issue.

Please reply ASAP..
Thanks
Selva
Employee

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

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;
One Star

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

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.
One Star

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

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
Employee

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

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