How to trim a field value at a load from database?

[#1]

Hi,

I am loading a field from a database, the record values needs to be trimmed on both beginning and end

example:

00000063359701549001538000000000

I need the first six 0's and last 8 0's to be removed, min you the 9th 0 from the end is needed, I don't want that to be removed.

Any ideas?

[Michiel_QV_Fan]

load step4&'0' as end_result;

load replace(step3, ' ', 0) as step4;

load rtrim(step2) as step3;

load LTrim(step1) as step2;

load

  replace(00000063359701549001538000000000, '0',' ') as step1

I separated the statements,Maybe you want to combine them in 1 string but this will do the job.

[Anonymous]

Hi,

if the number of digits in the record value are same,then its better to count the numbers and use left and right fucntions 'cause that's easy. Ex:

=left(right(00000063359701549001538000000000,26),18)
if not, then as Michiel explained we have to go round the bush..

HTH

ravi N.

[Michiel_QV_Fan]

Yes

[Anonymous]

(Y)

[yduval75]

Replace(LTrim(Replace([YourField], '0', ' ')), ' ', '0')

[#7]

Thanks guys!

I ended up saying:

mid(FIELDNAME,7,18) as Fieldname

Thank you