By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,262 Members | 1,128 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,262 IT Pros & Developers. It's quick & easy.

huh? select * from table where integer = '1a' ....

P: n/a
Why does a select from table with an integer field return a row if I compare
the integer to an alpha character?

Here's the info ( a straight copy and paste), then compare my two select
statements:

mysql> desc product;
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| prd_id | int(11) | | PRI | 0 | |
| prd_name | varchar(30) | | | | |
| prd_short_desc | varchar(250) | YES | | NULL | |
| prd_long_desc1 | text | YES | | NULL | |
| prd_long_desc2 | text | YES | | NULL | |
| prd_long_desc3 | text | YES | | NULL | |
| prd_thumb | varchar(250) | YES | | NULL | |
| prd_full_image | varchar(250) | YES | | NULL | |
| prd_price | decimal(8,2) | YES | | NULL | |
| prd_custom1 | varchar(10) | YES | | NULL | |
| prd_custom2 | varchar(10) | YES | | NULL | |
| prd_start_date | date | YES | | NULL | |
| prd_end_date | date | YES | | NULL | |
| prd_instock | int(11) | | | 0 | |
| prd_search_words | text | YES | | NULL | |
+------------------+--------------+------+-----+---------+-------+
15 rows in set (0.00 sec)

mysql> select prd_id from product where prd_id = 33;
+--------+
| prd_id |
+--------+
| 33 |
+--------+
1 row in set (0.01 sec)

mysql> select prd_id from product where prd_id = '33a';
+--------+
| prd_id |
+--------+
| 33 |
+--------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 3.23.32 |
+-----------+
1 row in set (0.00 sec)

mysql>
notice that
select prd_id from product where prd_id = 33;
and
select prd_id from product where prd_id = '33a';
both reutrun a row, the same row actually!

I noticed that this returns nothing:
select prd_id from product where prd_id = 'a33';

So 'What Gives?'

I can't seem to track down any info about this so any insight would be
great.
(please notice that my version is 3.23.32)

Thanks in advandce
JohnL

Jul 19 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
"John" <dsklfjhsdk> wrote in message
news:vt************@corp.supernews.com...
Why does a select from table with an integer field return a row if I compare the integer to an alpha character?

notice that
select prd_id from product where prd_id = 33;
and
select prd_id from product where prd_id = '33a';
both reutrun a row, the same row actually!

I noticed that this returns nothing:
select prd_id from product where prd_id = 'a33';

So 'What Gives?'

I can't seem to track down any info about this so any insight would be
great.
(please notice that my version is 3.23.32)

Thanks in advandce
JohnL

I would imagine it's a bug/feature of mysql that will truncate a number at
a character on input, in which case:

1) '33' = 33
2) '33a' = 33
3) 'a33' = nothing

try selecting something like this: select prd_id from product where prd_id =
'33a4';

and see what that gives you. Should just return 33 by my estimation.
Jul 19 '05 #2

P: n/a
"John" <dsklfjhsdk> wrote in message
news:vt************@corp.supernews.com...
Why does a select from table with an integer field return a row if I compare the integer to an alpha character?

notice that
select prd_id from product where prd_id = 33;
and
select prd_id from product where prd_id = '33a';
both reutrun a row, the same row actually!

I noticed that this returns nothing:
select prd_id from product where prd_id = 'a33';

So 'What Gives?'

I can't seem to track down any info about this so any insight would be
great.
(please notice that my version is 3.23.32)

Thanks in advandce
JohnL

I would imagine it's a bug/feature of mysql that will truncate a number at
a character on input, in which case:

1) '33' = 33
2) '33a' = 33
3) 'a33' = nothing

try selecting something like this: select prd_id from product where prd_id =
'33a4';

and see what that gives you. Should just return 33 by my estimation.
Jul 19 '05 #3

P: n/a
"SwissCheese" <Sw*********@cfl.rr.com> wrote in message
news:ZM*********************@twister.tampabay.rr.c om...
"John" <dsklfjhsdk> wrote in message
news:vt************@corp.supernews.com...
Why does a select from table with an integer field return a row if I compare
the integer to an alpha character?

notice that
select prd_id from product where prd_id = 33;
and
select prd_id from product where prd_id = '33a';
both reutrun a row, the same row actually!

I noticed that this returns nothing:
select prd_id from product where prd_id = 'a33';

So 'What Gives?'

I can't seem to track down any info about this so any insight would be
great.
(please notice that my version is 3.23.32)

Thanks in advandce
JohnL

I would imagine it's a bug/feature of mysql that will truncate a number

at a character on input, in which case:

1) '33' = 33
2) '33a' = 33
3) 'a33' = nothing

try selecting something like this: select prd_id from product where prd_id = '33a4';

and see what that gives you. Should just return 33 by my estimation.

That's partially correct. It's actually a feature of SQL in general. If SQL
is expecting a number and gets a letter, it drops everything after the
letter. Therefore:
33a = 33
a33 = nothing
3a3 = 3
Jul 19 '05 #4

P: n/a
Thanks for the replies.

sure enough in mysql '3a3' = 3

but in oracle it always fails on a 'non-number' character. (Oracle 8i)
Here's the output

SQL> select id from id_table where id = '6a7';
select plc_id from coop_placement where plc_id = '6a7'
*
ERROR at line 1:
ORA-01722: invalid number
I think I prefer getting an error in queries like this. Most of the time I
like it that mysql will take nearly any query and give it a shot, but in my
apps I'd rather it crapped out.

Thanks
JohnL
"Ryan Stewart" wrote in message news:fb********************@texas.net...
"SwissCheese" wrote in message
news:ZM*********************@twister.tampabay.rr.c om...
"John" <dsklfjhsdk> wrote in message
news:vt************@corp.supernews.com...
Why does a select from table with an integer field return a row if I compare
the integer to an alpha character?

notice that
select prd_id from product where prd_id = 33;
and
select prd_id from product where prd_id = '33a';
both reutrun a row, the same row actually!

I noticed that this returns nothing:
select prd_id from product where prd_id = 'a33';

So 'What Gives?'

I can't seem to track down any info about this so any insight would be
great.
(please notice that my version is 3.23.32)

Thanks in advandce
JohnL

I would imagine it's a bug/feature of mysql that will truncate a number

at
a character on input, in which case:

1) '33' = 33
2) '33a' = 33
3) 'a33' = nothing

try selecting something like this: select prd_id from product where

prd_id =
'33a4';

and see what that gives you. Should just return 33 by my estimation.
That's partially correct. It's actually a feature of SQL in general. If

SQL is expecting a number and gets a letter, it drops everything after the
letter. Therefore:
33a = 33
a33 = nothing
3a3 = 3

Jul 19 '05 #5

P: n/a
"John" <dsklfjhsdk> wrote in message
news:vu************@corp.supernews.com...
Thanks for the replies.

sure enough in mysql '3a3' = 3

but in oracle it always fails on a 'non-number' character. (Oracle 8i)
Here's the output

SQL> select id from id_table where id = '6a7';
select plc_id from coop_placement where plc_id = '6a7'
*
ERROR at line 1:
ORA-01722: invalid number
I think I prefer getting an error in queries like this. Most of the time I like it that mysql will take nearly any query and give it a shot, but in my apps I'd rather it crapped out.

Thanks
JohnL


I think you might need to start validating some variables before sending
your queries to the database...
Jul 19 '05 #6

P: n/a
"SwissCheese" <Sw*********@cfl.rr.com> wrote in message
news:ZM*********************@twister.tampabay.rr.c om...
"John" <dsklfjhsdk> wrote in message
news:vt************@corp.supernews.com...
Why does a select from table with an integer field return a row if I compare
the integer to an alpha character?

notice that
select prd_id from product where prd_id = 33;
and
select prd_id from product where prd_id = '33a';
both reutrun a row, the same row actually!

I noticed that this returns nothing:
select prd_id from product where prd_id = 'a33';

So 'What Gives?'

I can't seem to track down any info about this so any insight would be
great.
(please notice that my version is 3.23.32)

Thanks in advandce
JohnL

I would imagine it's a bug/feature of mysql that will truncate a number

at a character on input, in which case:

1) '33' = 33
2) '33a' = 33
3) 'a33' = nothing

try selecting something like this: select prd_id from product where prd_id = '33a4';

and see what that gives you. Should just return 33 by my estimation.

That's partially correct. It's actually a feature of SQL in general. If SQL
is expecting a number and gets a letter, it drops everything after the
letter. Therefore:
33a = 33
a33 = nothing
3a3 = 3
Jul 19 '05 #7

P: n/a
Thanks for the replies.

sure enough in mysql '3a3' = 3

but in oracle it always fails on a 'non-number' character. (Oracle 8i)
Here's the output

SQL> select id from id_table where id = '6a7';
select plc_id from coop_placement where plc_id = '6a7'
*
ERROR at line 1:
ORA-01722: invalid number
I think I prefer getting an error in queries like this. Most of the time I
like it that mysql will take nearly any query and give it a shot, but in my
apps I'd rather it crapped out.

Thanks
JohnL
"Ryan Stewart" wrote in message news:fb********************@texas.net...
"SwissCheese" wrote in message
news:ZM*********************@twister.tampabay.rr.c om...
"John" <dsklfjhsdk> wrote in message
news:vt************@corp.supernews.com...
Why does a select from table with an integer field return a row if I compare
the integer to an alpha character?

notice that
select prd_id from product where prd_id = 33;
and
select prd_id from product where prd_id = '33a';
both reutrun a row, the same row actually!

I noticed that this returns nothing:
select prd_id from product where prd_id = 'a33';

So 'What Gives?'

I can't seem to track down any info about this so any insight would be
great.
(please notice that my version is 3.23.32)

Thanks in advandce
JohnL

I would imagine it's a bug/feature of mysql that will truncate a number

at
a character on input, in which case:

1) '33' = 33
2) '33a' = 33
3) 'a33' = nothing

try selecting something like this: select prd_id from product where

prd_id =
'33a4';

and see what that gives you. Should just return 33 by my estimation.
That's partially correct. It's actually a feature of SQL in general. If

SQL is expecting a number and gets a letter, it drops everything after the
letter. Therefore:
33a = 33
a33 = nothing
3a3 = 3

Jul 19 '05 #8

P: n/a
"John" <dsklfjhsdk> wrote in message
news:vu************@corp.supernews.com...
Thanks for the replies.

sure enough in mysql '3a3' = 3

but in oracle it always fails on a 'non-number' character. (Oracle 8i)
Here's the output

SQL> select id from id_table where id = '6a7';
select plc_id from coop_placement where plc_id = '6a7'
*
ERROR at line 1:
ORA-01722: invalid number
I think I prefer getting an error in queries like this. Most of the time I like it that mysql will take nearly any query and give it a shot, but in my apps I'd rather it crapped out.

Thanks
JohnL


I think you might need to start validating some variables before sending
your queries to the database...
Jul 19 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.