Hello,
I need to find the position of the last space in a string of characters. e.g. if the string says - “How should I do this?”, then I need the position of the space before “this”. The ultimate goal is to get rid of the characters or numbers that follow (in this case it will be ‘this’). so, I was planning to use this as the length of the string in a Substr function.
Appreciate any help / suggestions.
Thanks,
Rekha.
RekhaG (BOB member since 2014-09-16)
Hi,
Is it known how many spaces there can be in the string? Or the max number of spaces?
This could be very hard to achieve in WebI. You’d better do that on a database level (ETL, universe) where you can use database functions that provide more features than what WebI offers in this regard.
Marek Chladny 
(BOB member since 2003-11-27)
Unfortunately no.
The number of spaces will vary as will the length of the string. The only piece that is consistent across all values is that the piece that needs to be truncated all start with - ‘29’…
I tried using POS() function along with Length() function and that doesn’t work. I couldn’t count the number of times a space was repeated nor could I count the first space from the end of the string i.e. going from R - to - L.
RekhaG (BOB member since 2014-09-16)
Hi,
Can you post several examples of possible values?
Marek Chladny (BOB member since 2003-11-27)
This isn’t so bad to calculate - use the REPLACE function to remove the spaces and then test the length of the string using LENGTH. Compare this to the unmodified length of the string, and this should give you the number of spaces in the string.
Doesn’t much help your original request though.
NMG
mcnelson 
(BOB member since 2008-10-09)
I don’t have WEBI open to validate, but in Excel for the below column A / B works:
Column A
This is 29 times
1872999345
there are 290 rivers
there are 90 rivers
there are 20 rivers
This is it
Column B (result)
This is
187
there are
there are 90 rivers
there are 20 rivers
This is it
Using this formula: =IF(ISERROR(FIND(“29”,A1)),A1,IF(FIND(“29”,A1)>0,LEFT(A1,(FIND(“29”,A1)-1))))
The isERROR captures the condition when “29” doesn’t exist. So, not sure if FIND function is allowed. Maybe a substr / locate argument may be allowed. It can probably be done, just need to pound it out.
If there are multiple occurrences of 29 and you need to find the last one to start the truncation… good luck.
datawizard (BOB member since 2015-01-20)
Please read the original requirement more carefully because the solution that you are suggesting above is quite different from what was asked by the original poster,
Marek Chladny 
(BOB member since 2003-11-27)
I’m sure you also read:
"The only piece that is consistent across all values is that the piece that needs to be truncated all start with - ‘29’… "
Maybe I read it wrong… sometimes you have to sift through the chaff to get the wheat.
If you are searching for multiple conditions for the far most right you would need a Case/When… or If/Then to test each char at end of string working left, when found process the truncation. Conditional statements are limited. Should be a Case or If within a loop to make it easier… depending on how many the max char would need to be tested to find the .
One last edit: The loop code would need to be generated at the database or in a derived table in the universe.
datawizard (BOB member since 2015-01-20)
I figure that if you absolutely have to do this in WEBI, the easiest approach might be to iteratively test the last n number of characters from the string using the RIGHT function. The minimum number is going to be 2 (as the space should occur in any 2 letter iteration due to the shortest word being a single character), and the max is, I reckon, whatever you think the longest word you’ll encounter might be - let’s say for example 28 characters (there aren’t many 28 letter words in English). This means you’ll need 13 iterations to safely discern where the space may be.
e.g:
Return the last 2 characters in the string and test if there’s a space in it. If not, increase the test length by 2 characters and test again.
You’ll end up with a real huge & ugly nested IF function but it should work.
HTH
NMG
mcnelson 
(BOB member since 2008-10-09)