DB2 Database Character comparison size

Encounter a Demand: Query page query conditions have a: By the xxx number start, xxx number cut-off query all data between two numbers ("XXX number" consists of alphanumeric).

When you encounter this problem, the first reaction is: How do you compare the size of the alphanumeric numbers together? Who's big and who's small?

So, I myself in the library (DB2) to modify enough data to test, the data (field name: "REVERSE1") is very simple 0-9,a-z,a-z, and some are null. A simple test:

1.sql statement: Select REVERSE1 from Syscompany order by REVERSE1;

2. Results:

3. Conclusion: The rules for the DB2 database's alphanumeric comparison size are:0-9<a<a<b<b<c<c<......<z<z;

But we are in the practical use, we encounter is certainly a good long alphanumeric combination of strings, the focus is not yet know how long the input letter, then for the unequal length of the string comparison size is what it looks like, I tried:

The SQL statements for the query criteria based on the slices are:

XXX number corresponds to: voucher0_. The Voucherregno field.

1. I did not enter the XXX number to inquire, the result:

2. I enter "VPDJP" from the "XXX number from:" Query condition, enter "Z" at the "to:" Query condition, and the result:

It can be seen that the comparison of the size of the time, is a character in the order of the previous one character to compare, if the previous character is small, do not look at the following characters.

Then I thought of another question, if the size of the whole string is judged by the previous character size, So if my character is: VPDJP0200000015001421313814152 and VPDJP020000001500142131381415200 comparison, which is the same as before, but there is no alphabetic number in the following comparison with "0", Who is big or small? Test it:

1. I enter "VPDJP0200000015001421313814152" in the "XXX number from:" Query condition, enter "VPDJP0200000015001421313814152" in the "to:" Query condition, the result:

2. I enter "VPDJP0200000015001421313814152" in the "XXX number from:" Query condition, enter "VPDJP020000001500142131381415200" in the "to:" Query condition, the result:

It can be seen that: in the case of the same character, there is no data after the "0" comparison, "0" is relatively large.

DB2 Database Character comparison size