Checking for numerics in character field

[David Abramowitz]

In RPG use the CHECK or CHECKR opcode. Dave

[padshore@optonline.net]

Sorry - I was'nt clear, but I am looking to do this in SQL Thanks anyway Alan

[Guest.Visitor]

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. Bill

[padshore@optonline.net]

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

[Guest.Visitor]

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. Bill

[buck.calabro@commsoft.net]

> 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 --buck

[Guest.Visitor]

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" (http://www.mc-store.com/5069.html) from MC Press. Mike <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6b240db3/6')

[padshore@optonline.net]

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 Alan

[padshore@optonline.net]

Buck/Bill - my apologies. I forgot to thank the both of you as well for your replies. Alan

[ukpi1b]

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. <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6b240db3/9')

[ukpi1b]

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 <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6b240db3/11')

[padshore@optonline.net]

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 Alan

[Guest.Visitor]

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. <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6b240db3/13')

[Guest.Visitor]

Awesome Ken... that one is going in my scrap book. Thanks. Chris

[padshore@optonline.net]

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. Alan

[dford@pmi.delta.org]

I believe Ken's solution works even in the case that padshore@optonline.net mentions. See below code ... <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6b240db3/16')

[padshore@optonline.net]

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). I know I could sub-string each byte out and check it that way, but if anyone knows of another method (irrespective of the size of the field) it would be much appreciated Thanks in advance Alan

[padshore@optonline.net]

DUH - You fool (Thats me speaking to me) I misunderstood/misread the way TRANSLATE worked. Sorry Ken Alan