String comparison in DB2
in [DB2]
|
From: spider007 on 19 Jun 2008 05:25 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.
From: Mark A on 19 Jun 2008 06:55 "spider007" <saurabh.saurabhjain(a)gmail.com> wrote in message news:0848c474-0404-4908-a8bb-40d8203e97d3(a)s21g2000prm.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.
From: spider007 on 19 Jun 2008 07:17 On Jun 19, 3:55 pm, "Mark A" <nob...(a)nowhere.com> wrote: > "spider007" <saurabh.saurabhj...(a)gmail.com> wrote in message > > news:0848c474-0404-4908-a8bb-40d8203e97d3(a)s21g2000prm.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
From: Mark A on 19 Jun 2008 07:35 "spider007" <saurabh.saurabhjain(a)gmail.com> wrote in message news:7cf49cdc-f573-4d22-b5ec-8d158ce39da8(a)u36g2000prf.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'?
From: spider007 on 19 Jun 2008 07:43
On Jun 19, 4:35 pm, "Mark A" <nob...(a)nowhere.com> wrote: > "spider007" <saurabh.saurabhj...(a)gmail.com> wrote in message > > news:7cf49cdc-f573-4d22-b5ec-8d158ce39da8(a)u36g2000prf.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? |