I am loading a field from a database, the record values needs to be trimmed on both beginning and end
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.
load step4&'0' as end_result;
load replace(step3, ' ', 0) as step4;
load rtrim(step2) as step3;
load LTrim(step1) as step2;
replace(00000063359701549001538000000000, '0',' ') as step1
I separated the statements,Maybe you want to combine them in 1 string but this will do the job.
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..
Replace(LTrim(Replace([YourField], '0', ' ')), ' ', '0')
I ended up saying:
mid(FIELDNAME,7,18) as Fieldname
Retrieving data ...