How to clean data

[its_anandrjs]

Hi All

I have a data in column and it is not in proper order like "-" is added in the numbers i want to remove that and want only numbers.

So how could i get that pls provide help.

Anand

[christian77]

Well:

replace(Field,'-','') as Field

If you have something like Barcelona - Real Madrid then use:

subfield(Field,'-',1)            as Field1

subfield(Field,'-',2)            as Field2

[erichshiino · Accepted solution]

If you just want to remove it,

you can write something like:

purgechar( Field,'-') as Field1

to remove - from your texto

or

left( Field, len(Field)-1) as Field1

to get all the characters at the left (except the last one)

Regards,

Erich

[its_anandrjs]

Thanks for that its work.

Regards

Anand

[#5]

Hi, Do I have to put this in the script file and reload?

[erichshiino]

Yes, that would be part of your load script,

lets say that you code is:

load a, b, c from abc.xls; (just an example.. you actually need more paramenters, right ?)

you can change it to:

load a, b,

purgechar( c,'-') as c

from abc.xls ;

[#7]

Thanks Erich