Tweet
In RPG use the CHECK or CHECKR opcode. Dave
-
-
Checking for numerics in character field
Sorry - I was'nt clear, but I am looking to do this in SQL Thanks anyway Alan -
Checking for numerics in character field
padshore wrote: > Hi everybody - I'm hoping someone knows the answer to this problem. I > need to select records from a file based upon numerous criteria, of > which one of the criteria is a 9 byte CHARACTER field containing ONLY > numeric values (0 through 9 for ALL 9 bytes). Alan, Could you give an example of what data you would want to exclude and include? It seems that your narrative and subject don't agree. BillComment
-
Checking for numerics in character field
To make things simple, lets just say that I am selecting data (via SQL) based upon one criteria. That criteria is that a 9 byte field is numeric. For example '123456789' would be selected '123A56789' would NOT be selected ' ' (all blanks) would NOT be selected ' 12345678' would NOT be selected '999999999' would be selected Like I said in my original posting, there are other selection criteria, but the question is only on THIS selection criteria. I could substring and inquire upon each of the individual bytes, but what if the field is 240 bytes long. In COBOL, this would be easy. IF FIELD-A IS NUMERIC is there an equivalent in SQL? Thanks in advance AlanComment
-
Checking for numerics in character field
padshore wrote: > To make things simple, lets just say that I am selecting data (via > SQL) based upon one criteria. That criteria is that a 9 byte field is > numeric. For example '123456789' would be selected '123A56789' would > NOT be selected ' ' (all blanks) would NOT be selected ' 12345678' > would NOT be selected '999999999' would be selected Like I said in my > original posting, there are other selection criteria, but the > question is only on THIS selection criteria. I could substring and > inquire upon each of the individual bytes, but what if the field is > 240 bytes long. In COBOL, this would be easy. IF FIELD-A IS NUMERIC > is there an equivalent in SQL? Thanks in advance Alan Ok, you want to check as to whether a field is all numeric characters. I must caution you that it's not as straightforwards as your Cobol answer, in EBCDIC encoding a negative value is represented by an alpha character, so it might determine that string '12345678R' is a valid number. I think this would be a perfect instance to implement a User Defined Function. You could create the UDF in whatever ILE language you'd like. I've created some functions and it's pretty straightforward once you get going. Use the website's Search function to find some relevant articles. BillComment
-
Checking for numerics in character field
> I think this would be a perfect instance to implement a User Defined > Function. You can also find an example in the FAQ at http://faq.midrange.com --buckComment
-
Checking for numerics in character field
The SQL function below will do exactly this. It accepts a string of up to 50 characters. The second parameter defines the length of the supplied string. If any non-numeric characters are found a value of 0 is returned. Otherwise a value of 1 is returned. Look for more SQL function samples in my new book "SQL Built-in Functions and Stored Procedures" from MC Press. Mike
CodeComment
-
Checking for numerics in character field
Mike - much appreciated. I am something of a newbie to SQL, and I was somewhat hesitant to attack creating a function, so again, much appreciated. I had already seen that your new book was available, and I was very interested. Again, thanks AlanComment
-
Checking for numerics in character field
Buck/Bill - my apologies. I forgot to thank the both of you as well for your replies. AlanComment
-
Checking for numerics in character field
If you processing one record at a time, check code below. Field contains one blank and # (not numeric values). SYSIBM/SYSDUMMY1 – one record dummy file provided by IBM. Query returns value (2) If numeric values not found, result is 0. Use your file and field names.
CodeComment
-
Checking for numerics in character field
Modify posted earlier code to get all records from file as shown in an example. QTEMP/XX has KK character field (length 5 for simplicity) You may use any selections in both parts of query depending on search criteria. One SQL statement does it. Performance issues should be taken in consideration
CodeComment
-
Checking for numerics in character field
ukpi1b - thanks for your replies. The idea of sub-stringing the field had already occurred to me, but what if the field is 400 (or god forbid, more) bytes long. The previous reply from Mike Faust is better suite, and is a relatively easy change to make if the field is more than 50 bytes long. However, thanks again for your input AlanComment
-
Checking for numerics in character field
You should be able to do it with the TRANSLATE function, as below. It will change any numeric characters to blanks, change blanks to 'z', and leave alone any other characters (including undisplayable ones). If the result is blank, then all of your initial characters were numbers. Note that the 2nd parm of TRANSLATE (to-value) is 10 blanks and a 'z', and the 3rd parm (from-value) is 0-9 and a blank.
CodeComment
-
Checking for numerics in character field
Awesome Ken... that one is going in my scrap book. Thanks. ChrisComment
-
Checking for numerics in character field
Ken - nice. However, be careful. If the field already contains (in this example) blanks or Z's, you have a problem. For example, if the field contains the value '12Z345 7890', this would be selected, when it is obviously a field that does NOT contain ALL numerics. AlanComment
Comment