Index Function

[#1]

could you please explain how index function working 

left( Date, index( Date,'-') -1 ) where Date = 1997-07-14 returns 1997

mid( Date, index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14 returns 07

Many Thanks

[#2 · Accepted solution]

Hi,

left( Date, index( Date,'-') -1 )

Here "index( Date,'-') -1" gives 5-1 which is 4 .

so left( Date,4) gives 1997

mid( Date, index( Date, '-', 2 ) -2, 2 )

Here

index( Date, '-', 2 ) gives the index of second occurence of '-' which is 8

so Mid(Date,8-2,2) = Mid(Date,6,2) = 07

[JonnyPoole]

In your example, the index function is finding the numerical character position of the  first  ( index( Date,'-')  )or second ( index( Date, '-', 2 ) )  instance of the hyphen ( - )  character in the string that is Date. 

Then there is a  subtraction of that number.  In the first case its 5 - 1 = 4.  2nd case its 8-6 = 6

That number is being fed into the left() and Mid() functions to take a piece of the date string.

left( Date , 4) -> first 4 characters of Date = 1997

mid( Date, 6, 2) -> starting at position 6, take next two characters = 07

[#4]

Hi,

left( Date, index( Date,'-') -1 ) where Date = 1997-07-14 returns 1997

index(date,'-') it will give by default first position of '-' that is 5 then left(date,5-1) is left(date,4) it will give finally 1997

mid( Date, index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14 returns 07

index(date,'-',2) it will give second '-' position that is 8 then 8-2 is 6

mid(date,6,2) it will give 07

[#5]

Thanks  Shruthi,

I was not under standing -1 use in firstexp, -2 use second exp .. thanks for help

[its_anandrjs]

Index is a string function

left( Date, index( Date,'-') -1 ) where Date = 1997-07-14 returns 1997

In this line index( Date,'-') finds the location of the ('-') symbol means here is 5 then 5-1=4 so from left 4 character picks that is 1997.

mid( Date, index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14 returns 07

In this index( Date, '-', 2 ) finds the second occurrence of the ('-') symbol that is * then 8-2 = 6 from 6 picks 2 charater by mid function that is 07

Regards

Anand