Extract Number from String

building reports webintelligence
1

In WebI I need to find a way to extract the numeric portion of a string. In DeskI, the tonumber(string) function works fine, but it doesn’t in WebI.

For example:

String…Numeric
1…1
2…2
3A…3
3B…3
4…4
4A…4
4B…4
4C…4
…
10A…10
…
100…100
100A…100
100Aa…100

Any ideas? Looks like in WebI, the numeric portion needs to be extracted before tonumber() can convert it. I’m not sure how to get rid of the random characters at the end.

Thanks!

JonTarz :us: (BOB member since 2006-01-09)

2

Create on object in Universe with below formula
case when soundex(“Character Field from Data base”) is not null then rtrim(“Character Field from Data base”,soundex(“Character Field from Data base”)) else “Character Field from Data base” end

Let me know is this works or not .

patriot3029 :india: (BOB member since 2006-12-08)

3

You can remove all the characters from the string and then use a to_number function.

How you do this would depend on your database. In Oracle you could use the translate function to get rid of the characters and then use to_number to convert it to a number.

jwhite9 :us: (BOB member since 2006-07-28)

4

Thanks for the suggestions, but I’m not looknig for a universe solution to this problem, WebI only.

I understand using the ToNumber() function in WebI, but removing the non-numeric characters is the hard part for me. There could be 1,2,3…x number of characters, so I can’t hardcode a left(trim([field]),2). Guess I’ll keep experimenting.

JonTarz :us: (BOB member since 2006-01-09)

5

Try formula in Universe which I posted , That formula is working fine for me

patriot3029 :india: (BOB member since 2006-12-08)

6

:flush:

As I posted previously, WebI only.

JonTarz :us: (BOB member since 2006-01-09)

7

Hi,

I am wondering if this will work (since I am writing the following formulas in Notepad only :slight_smile: ):

  1. Create a new report-level variable [alphacharacters] where all numbers from the original string will be removed:
= Trim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([string];"0";"");"1";"");"2";"");"3";"");"4";"");"5";"");"6";"");"7";"");"8";"");"9";""))
  1. And then check whether this formula gives you only numbers from the original string:
Replace(Trim([string]);[alphacharacters];"")

I am expecting the above formula to work if the non-numerical characters are only at the end, it means there are numerical characters at the beginning of the original string following by the non-numerical characters only.

So it won’t work for a string like 100AA3BB. But it should work for a string 1003AABB.

Can you check that? I don’t have an access to WebI now.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

8

Thanks Marek, that worked great!

I only changed one thing in the second formula, removed the space from the replace, so:

=ToNumber(Replace(Trim([string]);[alphacharacters];""))

I think that putting the example in the ‘code’ html tags automatically adds the space between the two quotes.[/code]

JonTarz :us: (BOB member since 2006-01-09)

9

There should not be any spaces between the double-quotes :?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

10

Yes, you’re right. I just copied and pasted it and there was no space. The first time I looked at it and typed the formula in, and at first glance it looked like there was a space.

JonTarz :us: (BOB member since 2006-01-09)

11

Hi

Having read through this post, it seemed the most appropriate to reply to! I would like to extract character instead of number! This maybe a basic question and I have looked at other topics on the forums but they do not seem to answer my question and this seems the closest I am going to get!

Basically my problem is this -

I have a string of letters which are never the same but are always the first few characters

could be AB or ABC for example - followed by 1 or 12 or 123

I wish to trim the trailing numeric characters.

I could have a trim function but as amount of characters are ever shifting this would not work. It seemed logical therefore to use a function to recognise when ever a number appeared remove them! (I also noted with a lot of the functions there seemed to be a lot of use of positioning of characters which would also not work - or I may be reading to much into the formulas!)

I will keep looking and trying with this, but any thoughts would be appreciated!

Thanks

Babelfish :uk: (BOB member since 2006-07-28)

12

Seems like this would work:

= Trim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([string];"0";"");"1";"");"2";"");"3";"");"4";"");"5";"");"6";"");"7";"");"8";"");"9";""))
13

Hi,

I am wondering if the formulas I wrote above (Thu Jul 08, 2010 10:17 pm) will work or not in your case as well. My guess is that it should work.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

14

Hi

Thanks for your replies!

I will try the formulas and see what happens

All help is always much appreciated! I just sometimes get stuck!

Thanks!

Babelfish :uk: (BOB member since 2006-07-28)

15

This worked like a charm!

Thanks :smiley:

Babelfish :uk: (BOB member since 2006-07-28)

16

Thanks!!

Babelfish :uk: (BOB member since 2006-07-28)