Stripping a zero out of the middle of a string

[laker_42]

I have a key that looks like this: HF000151. I need to take a zero out of it. I need help with a script to do this. I have over a 1000 records I need to apply this change to and I don't want to go through and delete a zero on every record. The one tricky thing is that there are two different size keys, one is 8 chars & the other is 9 chars. If anyone could help out it would be great!

Thanks,

John

[Pupiweb]

Make a calculated number field Length (TheKey) so that you can separate 8-digits from 9-digits keys ... you can get rid of it later on ...

The formula to remove a zero can be:

Left (TheKey,4) & Right (TheKey,3)

assuming the zeros are always in the same position

Once you've found the 8-digits keys run a Replace using the above formula, then use something similar for the 9-digits keys

[spb]

If the zero you need to strip is in the same place in every record, i.e., the third character from the left, then you could do it this way. Set up a new calculation field where the calculation is a Replace function. It should look like:

Replace(KeyField, 3, 1, "")

You are replacing starting from the 3 character from the left (the "3"), and making your replacement one character long (the "1"), and replacing with nothing (the double quote).

If your zero needs to be dumped from a different position in different records, then you're in trouble. But if your records always have a string of three zeros that need to become a string of two zeros, regardless of position, then you could use the Substitute function instead.

Once this is done, you can convert the new field from a calculation field to whatever you need the key field to be, rename it, delete the original key field, and move the new one into place. Or you could temporarily change your key field's definition to a calculation and make the calculation simply the name of the new field. Once the db has done that, redefine the Key Field back the way it was and delete your new field.

Steve Brown

[laker_42]

That's what I was trying to do. The zero IS always the third character from the left. So this will work. Thanks for the replies!

John