Characterizing data 1

[hallux]

Hello,
Some of the fields of type varchar that are supposed to only have account numbers have alpha characters in them. How do you all go about characterizing the data in a huge table? I want to know minimum/maximum values for numbers, string length, determine if leading or trailing spaces have been inserted, etc.

For starters I would like to use a query to check for alpha characters. When I am extracting the data for analysis, I begin finding that the application has inserted place holder dummy record fields during the time a field value is unknown and may not be pulling them out later. My purpose to make decisions from the data, not to fix/correct the application, which arguably might be programmed differently.

Thanks,
-Hallux

 

[hallux]

Followup: I found that at least in Oracle there is a min / max function to determine these values and also a length function.
The answer I can't find yet is how to query a field to check if there are any alpha characters, when it is only supposed to have numbers in the column. But since it is of datatype varchar, it can accomodate both.
Thanks,
-Hallux

 

[JarlH]

This might work (if all non-digit characters are letters):

if upper(col) = lower(col)

When upper case and lower case are equal, no letters are involved.

(If you have other non-digit characters, like %/&#¤, this solution wont work.)

 

[hallux]

Thanks, that worked.
This also helped:

http://www.techonthenet.com/oracle/questions/isnumeric.htm

Thanks,
-Hallux

 

[carp]

For future reference, posting your questions on one of the Oracle forums will get you an answer faster - questions identical to this question has already been answered several times in the past.