Hello everyone, my first post in the new version of this forum 
I’m using Webi 4.2, I need to get the last word in a string, the problem is there is no fixed pattern (number of spaces is variable. So how to have a formula to get the last word after the last space in the string?
Thank you in advance
Your title says “last word”, but your description says “last word before the last space.” My solution will give you the last word which I interpret to be the last word after the last space.
This is will be challenging in 4.2. I will try to show you some options. Maybe someone else will have a suggestion.
To begin I created a free-hand SQL query with the following SQL…
SELECT 1 AS [ID], ‘This is a phrase’ AS [SomeString]
UNION
SELECT 2, ‘And another one’;
My solution uses the RPos() function which looks like it was introduced in 4.3 SP1. You could also use the the Reverse() function, but that isn’t coming until 4.3 SP3.
I created a variable called Var Last Space and use RPos() to find the position of the last space in the string…
=Rpos([SomeString]; " ")
I use that in my next variable, Var Last Word…
=Right([SomeString];Length([SomeString])-[Var Last Space])
And there it is…
You could do this entirely in free-hand SQL. I did this in SQL Server, but this should be able to be adapted to whatever database you are using…
SELECT
x.*
, CHARINDEX (’ ‘, REVERSE (x.SomeString)) - 1 AS [LengthOfLastWord]
, RIGHT(x.SomeString, CHARINDEX (’ ', REVERSE (x.SomeString)) - 1) AS [LastWord]
FROM (
SELECT 1 AS [ID], ‘This is a phrase’ AS [SomeString]
UNION SELECT 2, ‘And another one’
) x;
That yields this with no need for any further variables…
I did not thoroughly test edge cases such as a space at the end of the string or two spaces before the last word. Hopefully, this sparks some ideas for you or a suggestion from someone else.
Noel
Hello Noel and thank you for your post. You were right, It was typing error, it should be last word “after” last space in the string. Unfortunately Rpos() and Reverse functions are not available in Webi 4.2, so above nice solution will not work.
What are the details of your data source (i.e., universe and database, excel, text, etc.)? Can you give the free-hand SQL option I mentioned at the end a try? If your data source is a universe and you have control of it or access to someone who does a Last Word dimension defined something like this is most certainly possible…
RIGHT(x.SomeString, CHARINDEX (’ ', REVERSE (x.SomeString)) - 1)
Keep asking questions. We can try to help you figure this out.
Hello Noel. The source of data is a universe and I do not have access to it. The data are Customer names that May be 2, 3, 4 or more names. So there may 1, 2, 3 or more spaces in this object.
Do you have Free-hand SQL as an option when you select a new data source in WebI?
I think that is going to be your best bet.
If that is not an option or you do not feel comfortable constructing your own free-hand SQL query you could do this in WebI by repeatedly search for a space from the left of your string until you do not find a space anymore. To do that you would take this as far as necessary which I think would be one more than the greatest number of spaces in your entire population.
Here are Var Space Pos 1 and Var Space Pos 2…
=Pos([Query 1].[SomeString]; " ")
=Pos([Query 1].[SomeString]; " "; [Var Space Pos 1] + 1)
...
To find that last word you have to find the first Var Space Pos X variable that evaluates to “0” and then back up from there…
=If [Var Space Pos 1] = 0 Then
[Query 1].[SomeString]
ElseIf [Var Space Pos 2] = 0 Then
Substr([Query 1].[SomeString]; [Var Space Pos 1] + 1;Length([Query 1].[SomeString])-[Var Space Pos 1])
ElseIf [Var Space Pos 3] = 0 Then
Substr([Query 1].[SomeString]; [Var Space Pos 2] + 1;Length([Query 1].[SomeString])-[Var Space Pos 2])
ElseIf [Var Space Pos 4] = 0 Then
Substr([Query 1].[SomeString]; [Var Space Pos 3] + 1;Length([Query 1].[SomeString])-[Var Space Pos 3])
ElseIf [Var Space Pos 5] = 0 Then
Substr([Query 1].[SomeString]; [Var Space Pos 4] + 1;Length([Query 1].[SomeString])-[Var Space Pos 4])
Are trying to extract the surname from the full customer name? That is going to be very problematic for names like Vincent Van Gogh, Leonardo da Vinci, and Oscar de la Renta where the surname is not always just the last word.
Since we are talking about names generally this is something good to think about…
Falsehoods Programmers Believe About Names
Noel
Hello Noel,
Actually I’m not able to test this, as there something missing here:
=Pos([Query 1].[SomeString]; " " ; [Var Space Pos 1] + 1)
Tried to modify to =Pos([Query 1].[SomeString]; " " + [Var Space Pos 1] + 1) but did work.
Thank you,
Ahmed
First you have to create Var Space Pos 1 as…
=Pos([Query 1].[SomeString]; " ")
And then create Var Space Pos 2 as …
=Pos([Query 1].[SomeString]; " "; [Var Space Pos 1] + 1)
And then create Var Space Pos 3 as …
=Pos([Query 1].[SomeString]; " "; [Var Space Pos 2] + 1)
And keep going until you have accounted for the greatest number of possible spaces. It wouldn’t hurt anything to have extra levels.
What is not working for you? Can you post a screen shot as did showing your variable values?
Noel
pay attention to the BO version!
only since 4.3 SP1 pos() is extended with start/end-parameter
Good catch. It would be helpful if SAP would list in their documentation in what version a function or an additional feature of a function was added. I know I can change versions in the help and go back until what I am looking at disappears.
I will see if I can come up with something that will work for you.
This approach should work in 4.2.
Find the first space
Substring from the first space to the end
Repeat until no more spaces are found
Let’s start with Var 4.2 Space Pos 1 and Var 4.2 String After Space 1, respectively…
=Pos([Query 1].[SomeString]; " ")
=Substr([Query 1].[SomeString]; [Var 4.2 Space Pos 1] + 1;Length([Query 1].[SomeString])-[Var 4.2 Space Pos 1])
And then Var 4.2 Space Pos 2 and Var 4.2 String After Space 2, respectively…
=Pos([Var 4.2 String After Space 1]; " ")
=Substr([Var 4.2 String After Space 1]; [Var 4.2 Space Pos 2] + 1;Length([Var 4.2 String After Space 1])-[Var 4.2 Space Pos 2])
And you just keep going for the maximum words (number of spaces + 1) possible in your data population.
Once the spaces are exhausted you will be left with the last word from your original string.
Or you can put it all together explicitly in a variable like Var 4.2 Last Word Brute Force…
=If [Var 4.2 Space Pos 1] = 0 Then
[Query 1].[SomeString]
ElseIf [Var 4.2 Space Pos 2] = 0 Then
[Var 4.2 String After Space 1]
ElseIf [Var 4.2 Space Pos 3] = 0 Then
[Var 4.2 String After Space 2]
ElseIf [Var 4.2 Space Pos 4] = 0 Then
[Var 4.2 String After Space 3]
Else
[Var 4.2 String After Space 4]
Does that work for you?
I feel obligated to point out this is a prime example of why components of names should each be stored in their own database column. I understand that you cannot change that.
Noel
Thank you very much Noel. That is wonderful.
