Get started Bring yourself up to speed with our introductory content.

Select records based upon numerous criteria

Within SQL, I need to select records based upon numerous criteria, but the one criteria that is leaving me scratching my head is select a record is a 9 byte alphanumeric field is strictly numeric (000000000 through 999999999). Using a range of '000000000' through '999999999' does NOT work, for example '0000000A0' aslo falls into that range. Any help would be much appreciated.
'0000000A0' should not be returned with a Between clause such as: alphanum_column BETWEEN '000000000' AND '999999999' due to the fact that the default Hex EBCDIC encoding of '0000000A0' is outside of that range. It could be that your application is utilizing a sort sequence that causes this non-numeric string to be included in that range.

It's confusing that you say that your alphanumeric column contains "strictly numeric" values, but asking about a non-numeric value like '0A0'. If this is a hexadecimal value that you're storing, you would need to write a UDF (user-defined function) to convert the column to a numeric value. If the column truly contains numeric values, you could change the range check to INTEGER(alphanum_column) BETWEEN 0 AND 999999999.


Visit the ITKnowledge Exchange and get answers to your DB2 questions fast.

Check out this Search400.com Featured Topic: Expert advice on DB2

Search400.com's targeted search engine: Get relevant information on DB2/400.

The Best Web Links: Tips, tutorials and more.

Dig Deeper on iSeries SQL commands and statements

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.