Re: [sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-14 Thread Daniel Lee Kruse 
D. Richard Hipp wrote:
George Ionescu wrote:
However, wanting to test how the engine compares strings and numbers:
SELECT 'match' WHERE '500' = 500;
returns 'match'; also, the following statements return the same result:
SELECT 'match' WHERE '500' = 500;
SELECT 'match' WHERE '500' = 499 + 1;
Who can tell me what other SQL database engines do with
the following?
Tested with Postgres V7.3.2:
   CREATE TABLE test1(a VARCHAR(100));
CREATE TABLE
   INSERT INTO test1 VALUES('501');
INSERT 24969 1
   INSERT INTO test1 VALUES('  502  ');
INSERT 24970 1
   SELECT * FROM test1 WHERE a=501;
  a
-
 501
(1 row)
   SELECT * FROM test1 WHERE a=502;
  a
-
(0 rows)
   SELECT * FROM test1 WHERE a<'502';
  a
-
 501
  502
(2 rows)
Or how about this:
   CREATE TABLE test2(b INTEGER);
CREATE TABLE
   INSERT INTO test2 VALUES(503);
INSERT 24973 1
   INSERT INTO test2 VALUES(504);
INSERT 24974 1
   SELECT * FROM test2 WHERE b='503';
  b
-
 503
(1 row)
   SELECT * FROM test2 WHERE b>'503';
  b
-
 504
(1 row)


--
Daniel Lee Kruse
Pursuant to U.S. code, title 47, Chapter 5, Subchapter II,Section 227, 
any and all unsolicited commercial E-mail sent,to this address is 
subject to a fee of $500.00 U.S. E-Mailing denotes, acceptance of these 
terms. Consult http://www.law.cornell.edu/uscode/47/227.html for details.

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-14 Thread George Ionescu 
Hello Dr. Hipp,
Hello SQLite users,

CREATE TABLE test1(a VARCHAR(100));
INSERT INTO test1 VALUES('501');
INSERT INTO test1 VALUES('  502  ');
SELECT * FROM test1 WHERE a=501;
SELECT * FROM test1 WHERE a=502;
SELECT * FROM test1 WHERE a<'502';

in MS SQL Server yelds the following results:

a

501

a

502

a

501
502

(please observe spacing from '  502  ', meaning that it's treated on output as a 
string (field's datatype from CREATE TABLE statement))

and the following:

CREATE TABLE test2(b INTEGER);
INSERT INTO test2 VALUES(503);
INSERT INTO test2 VALUES(504);
SELECT * FROM test2 WHERE b='503';
SELECT * FROM test2 WHERE b>'503';

produces:

b

503

b

504


Best regards,
George Ionescu

Re: [sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-13 Thread Doug Currie 
Firebird 1.5

SQL> CREATE TABLE test1(a VARCHAR(100));
SQL> INSERT INTO test1 VALUES('501');
SQL> INSERT INTO test1 VALUES('  502  ');
SQL> SELECT * FROM test1 WHERE a=501;

A
===
501

SQL> SELECT * FROM test1 WHERE a=502;

A
===
  502

SQL> SELECT * FROM test1 WHERE a<'502';

A
===
501
  502

SQL> CREATE TABLE test2(b INTEGER);
SQL> INSERT INTO test2 VALUES(503);
SQL> INSERT INTO test2 VALUES(504);
SQL> SELECT * FROM test2 WHERE b='503';

   B


 503

SQL> SELECT * FROM test2 WHERE b>'503';

   B


 504

e

Thursday, May 13, 2004, 4:42:24 AM, DRH wrote:

> George Ionescu wrote:
>> 
>> However, wanting to test how the engine compares strings and numbers:
>> 
>> SELECT 'match' WHERE '500' = 500;
>> 
>> returns 'match'; also, the following statements return the same result:
>> 
>> SELECT 'match' WHERE '500' = 500;
>> SELECT 'match' WHERE '500' = 499 + 1;
>> 

> Who can tell me what other SQL database engines do with
> the following?

> CREATE TABLE test1(a VARCHAR(100));
> INSERT INTO test1 VALUES('501');
> INSERT INTO test1 VALUES('  502  ');
> SELECT * FROM test1 WHERE a=501;
> SELECT * FROM test1 WHERE a=502;
> SELECT * FROM test1 WHERE a<'502';

> Or how about this:

> CREATE TABLE test2(b INTEGER);
> INSERT INTO test2 VALUES(503);
> INSERT INTO test2 VALUES(504);
> SELECT * FROM test2 WHERE b='503';
> SELECT * FROM test2 WHERE b>'503';




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-13 Thread Peter Boehm 
> Who can tell me what other SQL database engines do with
> the following?
>
>CREATE TABLE test1(a VARCHAR(100));
>INSERT INTO test1 VALUES('501');
>INSERT INTO test1 VALUES('  502  ');
>SELECT * FROM test1 WHERE a=501;
>SELECT * FROM test1 WHERE a=502;
>SELECT * FROM test1 WHERE a<'502';
Gupta's SQLBase:

TABLE CREATED

1 ROW INSERTED

1 ROW INSERTED

A

501
1 ROW SELECTED

A

0 ROWS SELECTED

A

501
  502
2 ROWS SELECTED

> Or how about this:
>
>CREATE TABLE test2(b INTEGER);
>INSERT INTO test2 VALUES(503);
>INSERT INTO test2 VALUES(504);
>SELECT * FROM test2 WHERE b='503';
>SELECT * FROM test2 WHERE b>'503';
>
>
Gupta's SQLBase:

TABLE CREATED

1 ROW INSERTED

1 ROW INSERTED

  B
===
503
1 ROW SELECTED

  B
===
504
1 ROW SELECTED

Peter

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-13 Thread Jarosław Nozderko 
Sybase ASE 12.5.1:

CREATE TABLE test1(a VARCHAR(100))
INSERT INTO test1 VALUES('501')
INSERT INTO test1 VALUES('  502  ')
SELECT * FROM test1 WHERE a=501
SELECT * FROM test1 WHERE a=502
SELECT * FROM test1 WHERE a<'502'

Result: "Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed."

CREATE TABLE test2(b INTEGER)
INSERT INTO test2 VALUES(503)
INSERT INTO test2 VALUES(504)
SELECT * FROM test2 WHERE b='503'
SELECT * FROM test2 WHERE b>'503'

Same as above. It requires explicit use of 'convert' function.

Regards,
Jarek

Jaroslaw Nozderko
GSM +48 601131870 / Kapsch (22) 6075013
[EMAIL PROTECTED]
IT/CCBS/RS - Analyst Programmer
 

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 13, 2004 10:42 AM
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] Re: SQLite version 3 design question: '500'=500?
> 
> 
> George Ionescu wrote:
> > 
> > However, wanting to test how the engine compares strings 
> and numbers:
> > 
> > SELECT 'match' WHERE '500' = 500;
> > 
> > returns 'match'; also, the following statements return the 
> same result:
> > 
> > SELECT 'match' WHERE '500' = 500;
> > SELECT 'match' WHERE '500' = 499 + 1;
> > 
> 
> Who can tell me what other SQL database engines do with
> the following?
> 
> CREATE TABLE test1(a VARCHAR(100));
> INSERT INTO test1 VALUES('501');
> INSERT INTO test1 VALUES('  502  ');
> SELECT * FROM test1 WHERE a=501;
> SELECT * FROM test1 WHERE a=502;
> SELECT * FROM test1 WHERE a<'502';
> 
> Or how about this:
> 
> CREATE TABLE test2(b INTEGER);
> INSERT INTO test2 VALUES(503);
> INSERT INTO test2 VALUES(504);
> SELECT * FROM test2 WHERE b='503';
> SELECT * FROM test2 WHERE b>'503';
> 
> 
> -- 
> D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
> 
> 
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> 
> 

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Fw: [sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-13 Thread Roy Black 
Hello,

In mysql:
mysql> SELECT * FROM test1 WHERE a=501;
+--+
| a|
+--+
| 501  |
+--+
1 row in set (0.02 sec)

mysql> SELECT * FROM test1 WHERE a=502;
+---+
| a |
+---+
|   502 |
+---+
1 row in set (0.00 sec)

mysql> SELECT * FROM test1 WHERE a<'502';
+---+
| a |
+---+
| 501   |
|   502 |
+---+
2 rows in set (0.00 sec)

and for the second table:
mysql> SELECT * FROM test2 WHERE b='503';
+--+
| b|
+--+
|  503 |
+--+
1 row in set (0.09 sec)

mysql> SELECT * FROM test2 WHERE b>'503';
+--+
| b|
+--+
|  504 |
+--+
1 row in set (0.00 sec)

Regards,

- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, May 13, 2004 1:42 AM
Subject: Re: [sqlite] Re: SQLite version 3 design question: '500'=500?


Who can tell me what other SQL database engines do with
the following?

CREATE TABLE test1(a VARCHAR(100));
INSERT INTO test1 VALUES('501');
INSERT INTO test1 VALUES('  502  ');
SELECT * FROM test1 WHERE a=501;
SELECT * FROM test1 WHERE a=502;
SELECT * FROM test1 WHERE a<'502';

Or how about this:

CREATE TABLE test2(b INTEGER);
INSERT INTO test2 VALUES(503);
INSERT INTO test2 VALUES(504);
SELECT * FROM test2 WHERE b='503';
SELECT * FROM test2 WHERE b>'503';


-- 
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]





-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-13 Thread Peter Pistorius 
I think it should return 0.

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-13 Thread George Ionescu 
Hello Dr. Hipp,
Hello SQLite users,

in MS SQL Server, the following line

SELECT '500' = 500;

returns a column having the alias '500' and the value 500 :-o

However, wanting to test how the engine compares strings and numbers:

SELECT 'match' WHERE '500' = 500;

returns 'match'; also, the following statements return the same result:

SELECT 'match' WHERE '500' = 500;
SELECT 'match' WHERE '500' = 499 + 1;

Hope I've been of some help.

Regards,
George Ionescu