Trim Character Field?

[forskare]

I have a character field, 8 characters in length. It is set to auto increment. I would like to trim all 0's to the left of the number. What expression would I write?

Or should the fields be numerical rather than character?

Thanks,

kenn

[Allen Klimeck]

RE: Trim Character Field?

This is a function I use


function lzerotrim as c(partnumber as c)
while (.not. (val(left(alltrim(Partnumber),1))>0.00))
if Isalpha(left(alltrim(Partnumber),1))
goto done
end if
partnumber = right(alltrim(partnumber),len(alltrim(partnumber))-1)

end while
done:
lzerotrim = partnumber

end

end function

[csda1]

RE: Trim Character Field?

Allen,

Ken may have all numerics, in which case it's as simple as

trimmedvalue=LTRIM(STR(VAL(charfieldvalue)))

On the other hand, your function can be greatly simplified

[b]
function lzerotrim as c(partnumber as c)
IF len(partnumber)

[Allen Klimeck]

RE: Trim Character Field?

Ira

I think that you left out a line but your script is clever. I always learn from your code

Allen

[csda1]

RE: Trim Character Field?

Allen,

Sorry, for some reason that line didn't copy. Here is what I sent.

[b]
function lzerotrim as c(partnumber as c)

IF len(partnumber)

[Stan Mathews]

RE: Trim Character Field?

What about

function findfst as C(str as C)
x = len(str)+1
for i = 1 to len(str)
if isdigit(substr(str,i,1)).and.val(substr(str,i,1))>0
findfst = substr(str,i,x-i)
end
end if
next
end function

Doesn't this eliminate the need for the ltrim()?

[Stan Mathews]

RE: Trim Character Field?

No, dang it, doesn't work if the first character is a character, not a digit.

[Stan Mathews]

RE: Trim Character Field?

Maybe

function findfst as C(str as C)

x = len(str)+1
for i = 1 to len(str)
if (isdigit(substr(str,i,1)).and.val(substr(str,i,1))>0).or.isalpha(substr(str,i,1))
findfst = substr(str,i,x-i)
end
end if

next
end function

[csda1]

RE: Trim Character Field?

Stan,

Ltrim is just there to get rid of leading spaces, which there shouldn't be any of.

Notice, in my version, it's loop is very simple. Yours will translate "AB00123CDE" to "123CDE". Is that what you intended?

The speed of my version will exceed pretty much any other version using a loop of some type. Yours also has a very complex IF expression.

You could also trim other characters by changing the while line to

while substr(partnum,i,1) $ " 0-"

This will trim any leading character found in the string constant.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[tlyon]

RE: Trim Character Field?

Oooh..a challenge, sort of :)
Here's another way to do it:

function zt as c(pn as c)
z = 1
while substr( pn, z, 1 ) = "0"
z = z + 1
end while
zt = if( z > 1, substr( pn, z ), pn )

I wrote this one before looking at Ira's, which is similar, but not nearly as efficient :)

Just kidding, Ira :)

Tom Lyon

[tlyon]

RE: Trim Character Field?

grr..let me try that paste once more:

function zt as C( pn as C )
z = 1
while substr( ltrim( pn ), z, 1 ) = "0"
z = z + 1
end while
zt = if( z > 1, substr( ltrim( pn ), z ), pn )
end function

Tom Lyon

[Tom Cone Jr]

RE: Trim Character Field?

Ken,

It's one thing to strip the leading zeros off when you use the field in an expression.

It's another thing altogether if you strip them off and then store the trimmed value back in the auto-increment field itself.

In this latter case the length of the character field will affect how auto-increment behaves. I encountered this recently and mention it now because it surprised me and my client.

Consider a char type field that's 5 wide, set to auto-increment.

If the last record entered in the table was given the value 9999, then what value do you expect the next record to receive when auto-increment does it's thing?

I would have said 10000, right?

Wrong.

It rolls back to 0001, because the value 9999 is only 4 characters wide, even though the structure of the table allocated space for 5 characters.

The solution if you want to use character type fields, and if you want to use numbers, and if you want to use all 5 of the available spaces is to incorporate leading zeros in each entry.

A field value of 09999 rolls over to 10000 just fine.

So, in my application I've learned the hard way that the leading zeros are my friends. -- tom

[JohnZaleski]

RE: Trim Character Field?

If the zeros really bother you could make a calculated field in the table. The calculated field would be based on the value in the auto increment field with the spaces trimmed. Something like:
right(str(val(autofield)),5)
might work.

[forskare]

RE: Trim Character Field?

Hi All,

In my situation, I had the field set to 8 characters but the client does't want to look at all the 0000000's. He suggested reducing the size of the field to 5 characters. After doing so, I only see 0000's and not the whole number. I went to the tables involved and changed the character lenght from 8 to 5, same for the format in the fields and checked the field rules. Same results.

So, keeping Tom Cone's suggestion in mind, and code from others, if I revert back to 8 characters, 999 should roll over to 1000. Am I right?

Thanks,

kenn

[Tom Cone Jr]

RE: Trim Character Field?

Ken,

nope. If it's a char type field then

00000999

will roll over to

00001000


but


999

will roll over to

001


-- tom