Substring

[elroy]

Hi all,
I have following issue:

I would like to get the abcd.xls from this string: /abc/def/ghijk/l/abcd.xls.

My idea:

substring('/abc/def/ghijk/l/abcd.xls', position('/', '/abc/def/ghijk/l/abcd.xls')+1, character_length('/abc/def/ghijk/l/abcd.xls'))

Unfortunately thats not correct because the function position returns the first position where the stated character is matched but I need the position of the last character (/).

Can anybody help?

Thanks and regards,
elroy

[blom0344]

Use the dot as reference point, cause it occurs only once in the entire string:

substring([dataitem],position('.',[dataitem])-4,100)

could be -5   ;D

[elroy]

Mhh. The lenght of the filename and path are not fix. That's the problem..
Any other ideas?

[blom0344]

Are you on SQL server and can you use RDBMS type functions? Then it would be a matter of 1/ reversing the string with  'reverse' function, then 2/ taking the right part out of it and then 3/ reversing the resulting part again:

reverse(substring(reverse([data_item]),1,position('/',[data_item])))

[elroy]

It works!
Many thanks :)

[DALSOM]

Hi,
I want to do above, but, in an text expression, with a parameter text variable!!! :o