Check IF Field is Numeric in AS/400 SQL

60 pts.
Tags:
AS/400 numeric fields
AS/400 SQL
SQL
SQL Server
is there a function to check if field is numeric (like ISNUMERIC in standard SQL)?

Software/Hardware used:
iSeries
ASKED: November 16, 2010  11:04 PM
UPDATED: March 12, 2011  1:05 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

xx

Discuss This Question: 13  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Not an answer, just a minor comment: ISNUMERIC is not part of the SQL standard. It is a vendor-specific function.
    65,110 pointsBadges:
    report
  • philpl1jb
    This site contains a couple of ways to resolve this issue best is a user defined function. Phil
    report
  • philpl1jb
    report
  • WoodEngineer
    Try the CHECK op code. It should do what you need.
    6,055 pointsBadges:
    report
  • WoodEngineer
    The %CHECK built-in function should also do the job.
    6,055 pointsBadges:
    report
  • TomLiotta
    Generally, SQL doesn't use a "ISNUMERIC" function because it shouldn't be necessary. There should be no way for non-numeric values to appear where numerics are defined. SQL verifies data when writing rows, so non-numerics are never allowed to get into SQL tables. An assumption is that catching such errors during a single write of a row reduces the need for the test for however many reads might happen in the future. The error is caught by monitoring over an UPDATE or INSERT. When UPDATE/INSERT fail, the resulting conditions tell you that values are incorrect. Since you should monitor and handle UPDATE/INSERT errors anyway, there's less sense in adding special code to make the same tests that SQL makes automatically and add that code in a different part of an SQL procedure. Why duplicate the tests? But that's just background. What exactly is the business problem that you have? Is this a SQL procedure that you are creating or changing? Is it a program that has embedded SQL? Or are you simply running SQL statements and want to report on potential errors in some transaction file? Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    The arguement for isnumeric is a character field, it returns a boolean. So the question is very clear, "is there a way to determine if a character field contains a valid numeric value." We don't need to know why the op asked that question, it's not so very unusual. WoodEngineer gave two good methods for analyzing the fields value in RPGLE. The site that I listed provided a couple of methods for doing this in SQL. Carlos pointed out that the function isn't part of the SQL standard. so SQL/400 doesn't fail to meet the SQL standards. Phil
    48,575 pointsBadges:
    report
  • TomLiotta
    We don’t need to know why the op asked that question, it’s not so very unusual. Generally true, but potentially misleading. With a glossed over set of assumptions, the best answer is probably to use TESTN. It'll give fewer false positives than CHECK/%CHECK and will easily outperform the SQL solutions. It might even work where other solutions need some testing -- The I compiled the IsNumeric() function and tested it. The image shows the source (after I corrected the syntax errors) which was directly copy/pasted and the results of a sample invocation. You can see that both '12345' and '!@#$%' returned 'Y' indications. It can be modified to work if you know enough about SQL and functions; but if you know that much, you probably aren't asking the question in this thread. The article is useful though. By reading about the first example, you can get an idea of why CHECK/%CHECK can be a problem. When "numeric" means values such as those that appear in ASCII files, characters that aren't digits become important. But CHECK/%CHECK don't count characters. A value like '12.345' is okay, but what about '1.2.3' or '.....'? So what is meant by "numeric" in a character field that TESTN won't handle? Without knowing some details, a good answer is tricky. Once we know what "numeric" is, it can be critical to know how the test will be used. A light-duty system that needs to check a few interactive values each day can waste time without impact. A high-volume web-site or batch validation system handling many thousands of transaction per hour or more might need to shave some milliseconds. Regardless, whatever answer is proposed should work. If another web page is referenced here, it should be described here and any problems with it should be pointed out. (There's no guarantee that a page on another site will even be available tomorrow.) Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Tom Great answer. Phil
    48,575 pointsBadges:
    report
  • TomLiotta
    A better answer would be one that helped, and I haven't done that yet. What we need is a general description of what is being done. Is there a file that has a field that needs testing? Can we see a couple examples? Is it interactive data entry? (Maybe it's a display file DDS problem.) Are these actual numeric values or just numeric digits such as a part number? Maybe it's a flat file that's being parsed and the value is between delimiters. There are lots of possible answers. I use atoi(), atof(), etc., in some cases where appropriate. Which answers are appropriate here? Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Tom If the OP needs more, they will have to provide more info. This fulfills your objective of leaving every question. however poor worded they are, with the best answer possible. I'm not sure about the link issue, I guess it's tha author in me. When someone has provided a solution to an issue they should get the recognition and maintain the control of that solution. When I see material that looks like it came from one of my texts, I study them carefully to make certain that they aren't lifted from my work..but, of course, noone cares, the books are available used for $2.50 plus shipping. Phil
    48,575 pointsBadges:
    report
  • Vatchy
    I will give you a good example of when you need to check for numeric data in a character field: I have a part number field that is a character field. This is a bill-of-materials app. Module numbers are seven digits, a dash, and two digits. That never changes. Part numbers, on the other hand, can have embedded spaces, character data, longer-shorter-same lengths, etc. I need to look at this part number and determine if it is a module or not.
    1,410 pointsBadges:
    report
  • TomLiotta
    Module numbers are seven digits, a dash, and two digits. That never changes. Part numbers, on the other hand, can have embedded spaces, character data, longer-shorter-same lengths, etc. I'd guess that the potential uses of an IsNumeric() function are understood. Yet, this example might partially show why it's not a SQL 'standard' function. The content of the field in this example appears to violate database design principles by encoding two separate elements of information in a single column -- an identity value and a type (module or part) value. I'd say that the better alternative is to have a separate column that identifies whether the item is a 'Part' or a 'Module'. Out of curiosity, if 'Part numbers' can have practically any combination of characters, how does the database prevent someone from creating a part number that has "seven digits, a dash, and two digits"? If such a 'Part number' was somehow entered into the database, how would it be determined that it wasn't a 'Module number'? There is a huge number and variety of such examples. Many databases have bits that violate or seriously bend design principles. That's why UDFs are allowed -- each database can have its own custom rules without requiring all SQL everywhere to be filled with every possible function or test we can imagine.
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following