Isolate Numeric values and AlphaNumeric Values from a Field

[kalyandg]

Hi All,

I have a requirement, need to isolate Numeric values and alpha numeric values from a Field.

For Example:

Item Code

12-45A-PGH

PG-123-ABC

As above, Itemcode starts with Numeric and Alphanumeric, I want to create two fields using a condition as below

if (ItemCode starts with 'Numeric value', 'Exports', (specification - 0,1,2,3,4,5,6,7,8,9)

if(ItemCode starts with 'Alphanumeric' , 'Imports', (specification - A-Z)

Please help me to write specific condition to extract the information as above.

Thanks,

Kalyan

[Anil_Babu_Samineni · Accepted solution]

May be this?

If(Left([Item Code], 1) = FindOneOf([Item Code],'0123456789'), 'Exports','Inports')

[techvarun]

Try below script

Test:

LOAD * INLINE [

    ItemCode

    12-45A-PGH

    PG-123-ABC

];

Load KeepChar(ItemCode, '0123456789') as Import, PurgeChar(ItemCode, '0123456789-') as Export Resident Test

[Peter_Cammaert]

Maybe a very compact expression will do the job?

IF (ord([Item Code]) < 58, 'Exports', 'Imports')

Technique is based on the fact that the ord() function returns the ASCII value of the first character of the string passed as a parameter. All alphabetical characters have a value larger than 64. This works perfectly as long as every product code starts with an akphanumeric character.