Removing character values and taking only numeric values from particular field

[#1]

Hi All,

I have one field as 'Plan_Id' which has got few initial values as characters and then numerical values as 001,002,003......

I need to bring only numeric values at UI level. Please suggest what function should be used.

Thanks,

Kirti

[#2]

Hi!

I think that you can use function KeepChar.

It works like this (copied from help file)

KeepChar(s1 , s2)

Returns the string s1 less all characters not contained in string s2.

Example:

keepchar ( 'a1b2c3','123' ) returns '123'

In your case s2 parameter should be like '0123456789'

Yours, Harri

[#3]

Hi Harri,

My field has values in database like:-

Non

Nop

05A

H1B

    0

001

002

003

004....

I need to remove first four and similar values which have characters and include only numeric values. Need to handle this in script but I am not sure which function will give me only numric values from one field.

Thanks,

Kirti

[alvinford]

Hi Try Using PurgeChar Function ..

Hope it Helps ...

Regards,

Alvin.

[alvinford]

Hi Try Using PurgeChar Function ..

Hope it Helps ...

Regards,

Alvin.

[swuehl]

Maybe something like this?

LOAD

Field

FROM Table where not findoneof( lower(Field),'abcdefghijklmnopqrstuvwxyz');

or

LOAD

Field

FROM Table where len(purgechar(Field,'1234567890'))=0;

Hope this helps,

Stefan

[jonathandienst]

Hi

Using PurgeChar could still leave a number if it was embedded in a string (eg A2B will return 2), which may not be what you want. I suggest that you use the IsNum(expr) function which returns true (-1) if the expr is numeric, or false (0) if not.

     If(IsNum(expr), expr) As Value

Regards

Jonathan

[#8]

Hi Stefan,

LOAD

Field

FROM Table where len(purgechar(Field,'1234567890'))=0;

is working perfectly. Thanks a lot.

Regards,

Kirti

[swuehl]

Good to hear, but I tend to complicate things from time to time and Jonathan pointed correctly to a much better solution using isnum():

LOAD

Field

FROM Table where isnum(Field);

[joseduque]

if(Plan_Id=Plan_Id,Null(),replace(ltrim(replace(Plan_Id,'0',' ')),' ','0')) as Plan_Id