Extract numbers from string Webi BO

Question

I'm almost new to Webi BO. I need to extract numbers from string. The string looks like below: x Y Z where x is variable length text, Y is the required number to be extracted (also may vary in length 0, or 00 or 000 or 0.00, 00.00, or 000.0, ... and Z is a fixed length text of 5 characters.

example for string:

sodium chloride 0.9% + Potassium chloride 50 mL/hr

x is sodium chloride 0.9% + Potassium chloride Y is 50 Z is mL/hr

The value of Y is needed to be extracted. Is there any formula to help with this regard? Thank you.

score 0 · Answer 1 · answered Jan 04 '21 at 22:35

This is not possible natively in Web Intelligence. Since X is variable in length and Z is a fixed length the only way see solving this is by looking for that space in front of Y which is possible if we reverse the string. However, there is no reverse function in WebI. Could you do this in your universe or in free-hand SQL?

This is what it would look like in SQL Server...

DECLARE @MyString VARCHAR(100);
DECLARE @Length INT;
DECLARE @Y_Index INT;
DECLARE @Y_Length INT;
DECLARE @Z_Index INT;

SET @MyString = 'sodium chloride 0.9% + Potassium chloride 50 mL/hr';
-- get total string length
sET @Length = LEN(@MyString);

-- find index (starting position) of Y
SET @Y_Index = @Length - CHARINDEX(' ', REVERSE(@MyString), 7) + 2;

-- find index (starting position) of Z
SET @Z_Index = @Length - CHARINDEX(' ', REVERSE(@MyString)) + 2;

SELECT
    @MyString                                                  AS [MyString]
  , REVERSE(@MyString)                                         AS [MyStringReversed]
  , @Length                                                    AS [MyStringLength]
  , @Y_Index                                                   AS [Y Index]
  , @Z_Index                                                   AS [Z Index]
  , RTRIM(SUBSTRING(@MyString, @Y_Index, @Z_Index - @Y_Index)) AS [Y];

Here is a link see it in action.

A string function to search a string in reverse called RPos was released in [4.3 SP1](https://blogs.sap.com/2020/12/01/sap-bi-4.3-sp1-whats-new-in-web-intelligence-and-semantic-layer/). If you have that version or higher my solution could be adapted to it. — Isaac, Jan 05 '21 at 01:48
Thanks Issac, but as mentioned I'm very new to Webi and I do not have enough experience with SQL codes, I tried another work around in 2 steps: firstly removed " mL/hr" with below function in Step1 variable, = Right( Replace([Main String]; " mL/hr" ;"") ;6), so the main string is shortened to few characters with at least single space before the digits, then in another variable Step2 I used =ToNumber(Substr( [Step1] ;Pos( [Step1] ;" ")+1;99)). — user72343, Jan 05 '21 at 10:00

Extract numbers from string Webi BO

1 Answers1