What index function does in SAP Data Services?

Question

I am looking at a data flow in SAP Data Services (BODS). One of the column 'Reg' in a table (WORKCODE) has double values (for e.g. 8.33E-02). Can anyone please explain the below expression and what is the function of 'index' in the below expression:

substr(WORKCODES.Reg, 1, (index (upper ( WORKCODES.Reg ),'E',1 )-1 ) )

Answer 1

I've just seen your post cause I'm looking for information about Index function in BODS. The index function returns the position of a character in a string value. The equivalent sql function is INSTR.

In your example (for e.g. 8.33E-02):

SUBSTR( '8.33E-02', 1, INDEX ( '8.33E-02', 'E', 1)-1 ) 

==> INDEX ( '8.33E-02', 'E', 1) will return the position of the letter 'E' starting at position 1. It should be 5.

Then you use SUBSTR function to extract the first part of your number :

SUBSTR( String Value, Start Pos, End Pos )
SUBSTR( '8.33E-02',       1,           INDEX ( '8.33E-02', 'E', 1)-1 )
SUBSTR( '8.33E-02',       1,           5-1 )

You subtract 1 to the position of 'E' to keep the first part !

It's never too late to answer ! =)

SQL Equivalent :

SELECT SUBSTR( '8.33E-02', 1, INSTR ( '8.33E-02', 'E')-1 ) 
     , INSTR ( '8.33E-02', 'E')
FROM DUAL

Flavour