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
(post deleted by author)
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.