Trim,PuregChar or Subfield

[#1]

Hi

I have some Data as follows:

X123

A09X

K85X

A12 D

B56 A

I am trying to tidy the data to resemble

X123

A09

K85

A12

B56

By using PurgChar it removes all The X,D & A's which is not my desired result.

Can anyone suggest a successful way of doing this?

Thanks

[Anonymous]

Hi,

It depends on the rule you are applying.

If you have a concrete small number of conversions, i would do by if statement.

If you are able to think in a concrete rule, then let's see...

Otherwise you can load a conversion table with input and output fields, and join it after load your data.

Marc.

[MK_QSL]

Left(Field,1) & PurgeChar(Trim(SubField(Field,' ',1)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as NewField

[MayilVahanan]

HI

Try like this

Load *, If(IsNum(Right(Field, 1)), Field, Trim(Mid(Field, 1, Len(Field)-1))) as Result Inline

[

Field

X123

A09X

K85X

A12 D

B56 A

];

[PrashantSangle]

Hi,

Try like

Temp:

LOAD * INLINE [
TempData
X123
A09X
K85X
A12 D
B56 A
];

load TempData,
right(TempData,1) as testTemp,
if(WildMatch(right(TempData,1),'1','2','3','4','5','6','7','8','9','0'),TempData,Mid(TempData,1,LEN(TempData)-1)) as new_data
resident Temp;

DROP Table Temp;

Regards

[#6]

Thanks All.

I have ended up with a function within the extracting database to trim off the various characters before extraction. All you help was much appreciated.

Thank You

[rwunderlich]

You want to remove the non-numerics from the right side, correct? I think Manish was on the right track but I would code it as

=left(Field,1) & KeepChar(mid(Field,2),'0123456789')

-Rob