CODEWITHSUNDEEP
sql-serverdatabasetransformationetlinformatica
mike
mike

Reputation: 47

Informatica Code to SQL Code

I am transforming the following informatica code to SQL. I am encountering some issues and would appreciate help with the following code: SUBSTR(COV_REINS_CONCAT_BK,INSTR(COV_REINS_CONCAT_BK,'|',1,3) +1,2)

That is, I am looking for the equivalent code to produce the same results in SQL Server.

I appreciate anyone's help!

Upvotes: 0

Views: 233

Answers (1)

Cosmin Rus
Cosmin Rus

Reputation: 418

SUBSTR's equivalent is SUBSTRING.

INSTR's equivalent is CHARINDEX, but it has the first 2 parameters reversed, and does not support the 4th parameter (occurrence).

The expression returns 2 characters after the third occurrence of | (pipe). Example: It will return 'FG' for 'A|BC|DE|FGH'.

So the translation will be:

SUBSTRING(COV_REINS_CONCAT_BK,1+CHARINDEX('|',COV_REINS_CONCAT_BK,1+CHARINDEX('|'
  ,COV_REINS_CONCAT_BK,1+CHARINDEX('|',COV_REINS_CONCAT_BK))),2)

Upvotes: 1

Related Questions