getting rid of those darn zero's

[intel2000]

See my FM field below,

FM
00065000
00028000
00110000
00116000
00080000
00090000
00042000
00245000
00120000
00130000
00260000
00063000
00430000
00145000
00025000
00114000
00090000

Is there a quick replace command that I can use to pull out all these zero's?
Also if there is, is there a command that I can use to pull out all the zero's out of the filed below at the same time??

FP
000628
000287
001200
001420
001200
000750
000900
002400
001289
001700
002500
000800
004000
001600
000400
001200
001200

 

[rgbean]

It's not clear what you need. Do you want to kill both the leading and trailing zeroes? (Does 00065000 end up, 65000, 65, or 065000?) Do you want them all the same length? (2, 3, 4, 5, 6?)

Rick

 

[intel2000]

Sorry about not being clear. If the number is 00065000 I want to end up with 65000. If the number is 00000450, I want to end up with 450. I would like to keep the field the same length just removing the previous zeros.

example:
0600 = 600

 

[rgbean]

Ok then to "fix" the FM field:

USE thetable in 0
SELECT thetable
lnLength = len(thetable.FM) && Appears to be 8, but just in case
REPLACE ALL FM with STR(VAL(FM), lnLength)

This will make the numbers right justified in the field with leading spaces instead of zeros.

Rick

 

[intel2000]

Yes, that works however I will be using this command inside a loop. Some indexes may not have that FM field, if thats the case im sure that an error would be generated. Is there another way I can word this solution to avoid this?

lnLength = len(thetable.FM)
REPLACE ALL FM with STR(VAL(FM), lnLength)

 

[DSummZZZ]

Try this:

IF TYPE('thetable.FM') = 'C'
REPLACE ALL FM with STR(VAL(FM), lnLength)
ENDIF
Dave S.

 

[intel2000]

IF TYPE('thetable.FM') = 'C'
REPLACE ALL FM with STR(VAL(FM), lnLength)
ENDIF


In the firstline I'm getting confused, what is C?

 

[DSummZZZ]

TYPE() returns the data type of the value passed to it. 'C' says the data type of FM is Character. If the field doesn't exist in the table, TYPE() will return 'U', for Undefined.
Dave S.

 

[intel2000]

Thanks Dave, your a huge help!!!!!!!!!!!!!!

 

[rob444]

Or you could try:

REPLACE ALL FM WITH IIF(TYPE(FM)='C',TRANSLATE(VAL(FM),'999999'),TRANSLATE(FM,'999999'))

If you want the zero's to appear again:

REPLACE ALL FM WITH IIF(TYPE(FM)='C',TRANSLATE(VAL(FM),'@L 999999'),TRANSLATE(FM,'@L 999999'))

Rob.

 

[rob444]

And of course the "TRANSLATE" function in my previous response should be "TRANSFORM".
I usually only use TRAN() instead of the full name...

Rob.