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]

Re: String comparison in DB2

"spider007" <saurabh.saurab hjain@gmail.com wrote in message
news:0848c474-0404-4908-a8bb-40d8203e97d3@s2 1g2000prm.googl egroups.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]

Re: String comparison in DB2

On Jun 19, 3:55 pm, "Mark A" <nob...@nowhere .comwrote:

"spider007" <saurabh.saurab hj...@gmail.com wrote in message
>
news:0848c474-0404-4908-a8bb-40d8203e97d3@s2 1g2000prm.googl egroups.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]

Re: String comparison in DB2

"spider007" <saurabh.saurab hjain@gmail.com wrote in message
news:7cf49cdc-f573-4d22-b5ec-8d158ce39da8@u3 6g2000prf.googl egroups.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]

Re: String comparison in DB2

On Jun 19, 4:35 pm, "Mark A" <nob...@nowhere .comwrote:

"spider007" <saurabh.saurab hj...@gmail.com wrote in message
>
news:7cf49cdc-f573-4d22-b5ec-8d158ce39da8@u3 6g2000prf.googl egroups.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]

Re: String comparison in DB2

spider007 wrote:

On Jun 19, 4:35 pm, "Mark A" <nob...@nowhere .comwrote:

>"spider007" <saurabh.saurab hj...@gmail.com wrote in message
>>
>news:7cf49cd c-f573-4d22-b5ec-8d158ce39da8@u3 6g2000prf.googl egroups.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/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/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