UPDATED in July 15

Hi there,

Today I saw a interfesting question posted on BOBJ [Webi 4.x] Find a character in the string.

The OP questioned on how one can get the numeric value (characters from 0 to 9) that lies inside a text field .

For instance , the value of the field [Address Field] is “Houston77047TX” and “Zipcode” variable should report “77047” . You can deal, as well, with formatted numbers (like 12,345.78) as well as dates (like 07/15/2015) assuming that “.” , “,” and “/” only appears in the Number/Date to be extracted.

So, how to accomplish it ?

I came  up with one solution that consists on :

1 – Replace all numeric characters with another character, for instance “?” , so the previous [Address Filed] will become [Replaced] = “Houston?????TX”

2 – Find the position of the first ocurrence of “?” inside [Replaced]

3 – Find the position of the last ocurrence of “?” inside [Replaced]

4 – Extract [Number] from [Address Field] given the [Start] and [End] position of [Number] (given in steps 1 and 2.

To accomplish that, I create 4 variables,

[Replaced] =Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([var];”0″;”?”);”1″;”?”);”2″;”?”);”3″;”?”);”4″;”?”);”5″;”?”);”6″;”?”);”7″;”?”);”8″;”?”);”9″;”?”);”,”;”?”);”.”;”?”);”/”;”?”)

[Start] = Pos([Replaced;”?”)

[End] =If(Substr([Replaced];[Start]+1;1)=”?”;If(Substr([Replaced];[Start]+2;1)=”?”;If(Substr([Replaced];[Start]+3;1)=”?”;If(Substr([Replaced];[Start]+4;1)=”?”;If(Substr([Replaced];[Start]+5;1)=”?”;If(Substr([Replaced];[Start]+6;1)=”?”;If(Substr([Replaced];[Start]+7;1)=”?”;If(Substr([Replaced];[Start]+8;1)=”?”;If(Substr([Replaced];[Start]+9;1)=”?”;[Start]+9;[Start]+9);[Start]+8);[Start]+7);[Start]+6);[Start]+5);[Start]+4);[Start]+3);[Start]+2);[Start]+1)-1

For [End], I assumed that the maximum size of the number would be 9 characters long (from 0 to 999999999)  but the size is expandable, you can set the maximum number to whatever you want.

And, finally

[Number] = =Substr([Address Field];[Start];[End]- [Start]+1).

The idea behind these variables is the following :

[Replaced] : Search [Address Field] and recursevely change the characters from 0 to 9 with ?

[End] Trhough the [Start] position of “?” inside [Replaced], I test the [Start] +1 searching for “?”, if it´s found, I search at [Start] + 2 and so on, until [Start]+9. If “?” is not found at [Start]+n, returns [Start]+n.

I will post the wid sample of it later.

Regards,

Rogerio