String comparison in DB2

[spider007]

Hi All,

I created a table with only one char(1) column and inserted values
from 'A' to 'Z'. After that I am executing "select * from tablename
where colname between '0' and 'z'. It gives me all the data except
'Z' (capital Z). As I am not getting 'Z', it makes me wonder how does
DB2 compare the strings (converts it into ascii or ebcdic or something
else?) ? Anyone has an idea?

Thanks.

[Mark A]

"spider007" <sa*****************@gmail.comwrote in message
news:08**********************************@s21g2000 prm.googlegroups.com...

Hi All,

I created a table with only one char(1) column and inserted values
from 'A' to 'Z'. After that I am executing "select * from tablename
where colname between '0' and 'z'. It gives me all the data except
'Z' (capital Z). As I am not getting 'Z', it makes me wonder how does
DB2 compare the strings (converts it into ascii or ebcdic or something
else?) ? Anyone has an idea?

Thanks.

What operating system is DB2 running on, and what operating system is the
client running on? Also, it is generally a good idea to specify DB2 version
and fixpack you are using when asking a question, although it may not apply
to this question.

[spider007]

On Jun 19, 3:55 pm, "Mark A" <[e-mail]:

"spider007" <[e-mail] in message

news:08**********************************@s21g2000 prm.googlegroups.com...

Hi All,

I created a table with only one char(1) column and inserted values
from 'A' to 'Z'. After that I am executing "select * from tablename
where colname between '0' and 'z'. It gives me all the data except
'Z' (capital Z). As I am not getting 'Z', it makes me wonder how does
DB2 compare the strings (converts it into ascii or ebcdic or something
else?) ? Anyone has an idea?

Thanks.

What operating system is DB2 running on, and what operating system is the
client running on? Also, it is generally a good idea to specify DB2 version
and fixpack you are using when asking a question, although it may not apply
to this question.

I thought this was regarding the SQL, so didnt mention it. Anyways, i
am running 9.5 on Windows XP

[Mark A]

"spider007" <sa*****************@gmail.comwrote in message
news:7c**********************************@u36g2000 prf.googlegroups.com...

I thought this was regarding the SQL, so didnt mention it. Anyways, i
am running 9.5 on Windows XP

I don't think there would be ebcdic involved on DB2 for Windows, only ascii.
But you can check the database configuration for the ALT_COLLATE value.

What does the data look like when you select all the rows in the table
without a where clause, or with = 'Z'?

[spider007]

On Jun 19, 4:35 pm, "Mark A" <[e-mail]:

"spider007" <[e-mail] in message

news:7c**********************************@u36g2000 prf.googlegroups.com...

I thought this was regarding the SQL, so didnt mention it. Anyways, i
am running 9.5 on Windows XP

I don't think there would be ebcdic involved on DB2 for Windows, only ascii.
But you can check the database configuration for the ALT_COLLATE value.

What does the data look like when you select all the rows in the table
without a where clause, or with = 'Z'?

If I select all the values (select *), it shows me all the records
along with 'Z', however, if I mention a where clause, it does not show
'Z'. Here are the command which I executed:

db2 " insert into sj3 values ('A'),('B'),('C'),('X'),('Y'),('Z') "

db2 " select * from sj3 where a between '0' and 'z' "
A
-
A
B
C
X
Y

5 record(s) selected.

I just tried the same on 9.1 FP3 on Linux, and I got the o/p as
expected, meaning I am getting 'Z' with the above query. So it is
depending on the OS, but what is the difference?

[The Boss]

spider007 wrote:

On Jun 19, 4:35 pm, "Mark A" <[e-mail]:

>"spider007" <[e-mail] in message

news:7c**********************************@u36g200 0prf.googlegroups.com...

>>I thought this was regarding the SQL, so didnt mention it. Anyways,
i am running 9.5 on Windows XP

I don't think there would be ebcdic involved on DB2 for Windows,
only ascii. But you can check the database configuration for the
ALT_COLLATE value.

What does the data look like when you select all the rows in the
table without a where clause, or with = 'Z'?

If I select all the values (select *), it shows me all the records
along with 'Z', however, if I mention a where clause, it does not show
'Z'. Here are the command which I executed:

db2 " insert into sj3 values ('A'),('B'),('C'),('X'),('Y'),('Z') "

db2 " select * from sj3 where a between '0' and 'z' "
A
-
A
B
C
X
Y

5 record(s) selected.

I just tried the same on 9.1 FP3 on Linux, and I got the o/p as
expected, meaning I am getting 'Z' with the above query. So it is
depending on the OS, but what is the difference?

Read up on "collating sequence" here:
http://publib.boulder.ibm.com/infoce...d/c0006812.htm

In your case the coll. seq. on your Windows box is probably configurated
such that lowercase 'z' sorts lower as uppercase 'Z'.

HTH

--
Jeroen