Hi Everyone,



This can't be right...



It stuffs up on every Ingres version I've tried it on so far...

II 2.6/0604 (int.lnx/00) 12166. 23rd Apr 2009.

II 9.0.4 (a64.lnx/105)NPTL 12707. 23rd Apr 2009.

II 9.1.1 (a64.lnx/103)NPTL 13296. 23rd Apr 2009.



Try it!



\r

set autocommit on

\p\g

drop table test_string; \p\g

create table test_string(

pid integer not null not default,

a_string varchar(100) not null with default)

\p\g

insert into test_string(pid, a_string) values (1,''); -- empty string

insert into test_string(pid, a_string) values (2,''); -- empty string

insert into test_string(pid, a_string) values (3,''); -- empty string

insert into test_string(pid, a_string) values (4,' '); -- 1 space

insert into test_string(pid, a_string) values (5, ' '); --1 space

insert into test_string(pid, a_string) values (6, ' '); -- 2 spaces

\p\g



-- should be 3 not 6!

select count(*) from test_string where a_string='' -- empty string

\p\g



-- should be 2 not 6!

select count(*) from test_string where a_string=' ' -- 1 space

\p\g



-- should be 1 not 6!

select count(*) from test_string where a_string=' ' -- 2 spaces

\p\g



-- should be 0 not 6!

select count(*) from test_string where a_string=' ' -- 3 spaces

\p\g

\q

Hi,

As far as I remember, varchar does not store trailing spaces, so it's fine.... Maybe you should choose char?

Regards,
Artur Fajok


From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Martin Bowes
Sent: Thursday, April 23, 2009 1:36 PM
To: Ingres and related product discussion forum
Subject: [Info-Ingres] Why are these strings so spacey?

Hi Everyone,

This can't be right...

It stuffs up on every Ingres version I've tried it on so far...
II 2.6/0604 (int.lnx/00) 12166. 23rd Apr 2009.
II 9.0.4 (a64.lnx/105)NPTL 12707. 23rd Apr 2009.
II 9.1.1 (a64.lnx/103)NPTL 13296. 23rd Apr 2009.

Try it!

\r
set autocommit on
\p\g
drop table test_string; \p\g
create table test_string(
pid integer not null not default,
a_string varchar(100) not null with default)
\p\g
insert into test_string(pid, a_string) values (1,''); -- empty string
insert into test_string(pid, a_string) values (2,''); -- empty string
insert into test_string(pid, a_string) values (3,''); -- empty string
insert into test_string(pid, a_string) values (4,' '); -- 1 space
insert into test_string(pid, a_string) values (5, ' '); --1 space
insert into test_string(pid, a_string) values (6, ' '); -- 2 spaces
\p\g

-- should be 3 not 6!
select count(*) from test_string where a_string='' -- empty string
\p\g

-- should be 2 not 6!
select count(*) from test_string where a_string=' ' -- 1 space
\p\g

-- should be 1 not 6!
select count(*) from test_string where a_string=' ' -- 2 spaces
\p\g

-- should be 0 not 6!
select count(*) from test_string where a_string=' ' -- 3 spaces
\p\g
\q

Martin Bowes wrote:

Alas it can be, and it is. From the SQL Reference Manual, on the
subject of varchars: "If the strings being compared are unequal in
length,the shorter string is padded with trailing blanks until it
equals the length of the longer string."

What is more, I believe this is ANSI/ISO mandated behaviour, not an
Ingres-specific quirk.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

Thank You Roy!

So what we need to do is something more along the lines of:
select count(*) from test_string where a_string + 'X'=' ' + 'X'; -- two
spaces.

Marty

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Roy
Hann
Sent: 23 April 2009 13:33
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] Why are these strings so spacey?

Martin Bowes wrote:

Alas it can be, and it is. From the SQL Reference Manual, on the
subject of varchars: "If the strings being compared are unequal in
length,the shorter string is padded with trailing blanks until it
equals the length of the longer string."

What is more, I believe this is ANSI/ISO mandated behaviour, not an
Ingres-specific quirk.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9,
2009
Go to http://www.iua.org.uk/join to get on the mailing list.


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

Fajok, Artur, (ProService AT) wrote:

No, varchar *does* retain trailing spaces.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

On Apr 23, 2009, at 8:44 AM, Martin Bowes wrote:

That's one possibility. Or, you can say: where a_string='' and length
(a_string)=2
(I haven't tried that, but I don't believe that the length function
trims
trailing blanks -- I could be wrong). Or you can say a_string LIKE
' ',
because LIKE is defined to NOT ignore trailing spaces.

The trailing blank thing is a pain in the ass internally, and I wish it
didn't work that way; but it does.

Karl