detecting non-numeric characters in a field

[William Perry]

I have a field in a table that should have been defined as numeric when the table was established several years ago. Unfortunately, it was set up as a character field. I would like to go back now and change its type to numeric, but I want to clean up any non-numeric data that has been stored there in the mean time. Does anyone know of a function that would help me detect the soon-to-be non-conforming data? Thanks for any help.

-Bill

[Vince McGinnis]

RE: detecting non-numeric characters in a field

I believe you do this using the CHKDIGIT function, though this is not really what the function was designed for. The CHKDIGIT function will return an empty string if the character string is invalid (and a non-numeric characters would cause this).

Try doing a query on some test data using the expression:

CHKDIGIT(character fieldname)=""

I just did a test and it seemed to work fine.

[whanigsberg]

RE: detecting non-numeric characters in a field

isalpha might help.

Bill

[William Perry]

RE: detecting non-numeric characters in a field

Thanks to both of you guys. I will be trying both of these functions. I knew that there had to be a way!

-Bill

[Vince McGinnis]

RE: detecting non-numeric characters in a field

The Isalpha or Isdigit functions can be used to check a character string or field. By default these functions check the first character in a string. With a little code though, one could test every character in a character field, then step to the next field.

[Bill Warner]

RE: detecting non-numeric characters in a field

Expanding on Vince's idea, you could also examine the ASCII value of each character, since numbers are 048 thru 057.

[Stan Mathews]

RE: detecting non-numeric characters in a field

Save the following as a function. Add a field to your table, calculated, = Chkold(yourfieldname).

Sort your table on the new calculated field.


'************************************************
function Chkold as L(oldstr as C)
' Pass a character string to the function
' The locigal value is returned, true if it contains characters.

'set inital to assume no letters

Chkold = .F.
for i = 1 to len(oldstr)
if isalpha(substr(oldstr,i,1))
Chkold = .T. 'if letter found, quit
End
End function
end if
next



END
end function

[CALocklin]

RE: detecting non-numeric characters in a field

Also, decide if you REALLY need a numeric field or if you only need a character field that is limited to numerals.

The general rule is, "Use a character field unless you will be performing math operations with the value in the field." If necessary, set up a mask to limit the type of characters accepted and use PADL or PADR if you want to have leading/trailing characters to fill the field. Leading zeros is a good idea if you want to sort 'numerically' on a character field. EX: 20 will come before 3 because 2 comes before 3; but 003 will come before 020.

[Vince McGinnis]

RE: detecting non-numeric characters in a field

Would somone else verify the CHKDIGIT function?

I know this is not the intended purpose of the function, but I tested it several times and it worked great!

To use it, just do a query using the expression:

 CHKDIGIT(Fieldname)=""

I was so excited, I always wanted to use this function but don't deal with credit card numbers or barcodes, except 3 of 9.

Thanks

[William Perry]

RE: detecting non-numeric characters in a field

Cal: This is a good point, and one that I don't disagree with. In this case, I won't be doing math with the values in this field. But I do want the following: 1) to restrict the characters in the field to numerals, 2) to sort the values as if they are numbers, and 3) to allow the user to search the values in the field without entering leading zeros. I agree that all this can be done with numerals in a character field by 1)testing keyed or imported entries, 2)padding the values, and 3)creating an index that includes the str(val()) of the field's value. But in your experience, is this a case where breaking the "use character fields" rule would be appropriate in order to simplify the application? I am interested in your thoughts. Thanks,

-Bill

[William Perry]

RE: detecting non-numeric characters in a field

Stan: This function is great. I knew there was a way to do this with isalpha and substr, but I couldn't seem to simplify may code to something managable. The combination of FOR and LEN are great here. I tend to always use WHILE loops and forget about FOR, but that really makes for a concise function. Thanks again,

-Bill

[William Perry]

RE: detecting non-numeric characters in a field

Thanks to everyone. I want to experiment with CHKDIGIT too. I also don't do much with credit cards, etc. but this seems like a very useful tool.

-Bill

[Stan Mathews]

RE: detecting non-numeric characters in a field

I learned it all from Ira, bless him.

[CALocklin]

RE: detecting non-numeric characters in a field

Bill,

First, I see any problem with breaking rules as long as we know the consequences and you seem to understand them quite well.

The reason I made that comment in the first place was the fact that the data originally had non-numeric characters. This implied to me that there might be some need for this again in the future -or- it might be a situation where leading zeros (or other char) might be needed. EX: I work with a company that uses 8 digit part numbers but all the ones I deal with only have 7 'real' digits and it creates problems if I fill in their forms and barcode labels without the leading zero.

The only other reason I can think of right now for maintaining a character field would be if there might be a need to search on certain characters in the field. EX: One of my database customers uses a 'master number' field that starts with the first two digits as the year the job was completed.

So, as I said above, I see no problem with using a numeric field since you understand the implications and are therefore making an informed decision.

[William Perry]

RE: detecting non-numeric characters in a field

Thanks for your input, Cal. I was anxious to discuss the reasons behind this particular "best practice," as I often pick up some ideas from such a discussion, even when a particular situation callls for breaking the "rules."
-Bill