Using Index function

[jagadam]

Hi All,

I have a requirement where I have to capture the string from a line after a space in the reverse order.

eg: 1) james alex 1234 usa
2) johnny beck abc 1234 canada
output should be:
1)usa
2)canada

note: The number of spaces are not constant.

I tried to use the index function by mentioning the negative value like index(inputcol," ",-1) ,substring and field fucntion, but this is not working. Is there any simple way to implement this.

Thanks
NJ

[bob7027]

hey, u can do as count the total number of filed delimiters i.e spaces.
From there do -1 from that you will get the last string of that row,
let me know if you did not get this process.


Thx

[jagadam]

I believe count gives a number as output and i don't understand how doing a -1 on it will give the last string. please let me know if i missed anything.

Thanks
NJ

[bob7027]

use this: field ("james alex 1234 usa", DCount("james alex 1234 usa", " "))

let me knw if u didnt get it

thx

[prasson_ibm]

In your example,you have to count then apply field function.

eg: 1) james alex 1234 usa
Count(input," ") - 3
Count1- Count+1
Field(line," ",count1)
1)usa

[jagadam]

Hi Bob,

Your solution is working for the requirement that i mentioned in the post, thanks for that, but i forgot to mention complete solution that I am looking. Below is how i want the output to be

eg: 1) james alex 1234 usa
2) johnny beck abc 1234 canada
output (1)should be:
1)usa
2)canada
output(2) should be:
1)james alex 1234
2)johnny beck abc 1234

Please let me know if this makes sense.

Thanks
NJ

[chulett]

If you know how to take the last field, to get everything up to but not including the last field use "1, dcount-1" where you have "dcount" right now. For example:

Last field:

Code: Select all

Field(YourField, " ", DCount(Yourfield," "))

All other fields:

Code: Select all

Field(YourField, " ", 1, DCount(Yourfield," ")-1)