473,324 Members | 2,002 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

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
8 3895
"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
"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
"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
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
"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
"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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Landers, Jason | last post by:
I have two tables, meetings and tasks. In the meetings table I have meeting information and a meeting index called id. In the tasks table I have task information that is associated with various...
0
by: John | last post by:
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...
1
by: Sam G | last post by:
Hi folks, What I'd like to do for a website I'm designing with PHP/MySQL is have a number of registered users who can make friends with each other... so if person 1 wants to be friends with...
1
by: Michael | last post by:
I have a table that has the following fields: tblECHECK ID (autonumber identity column) PTID Batchnum Page DataPoint DPValue
3
by: Carmen Gloria Sepulveda Dedes | last post by:
Hello. When I run the next query: SELECT DATE_TRUNC('hour', TL.TAL005_DATE), TL.SRV_ID, TL.MSU_NUMBER, DS.DESCRIPTION, DS.CALLTYPE, COUNT(*) FROM OWNER_CATALOG.TAL005 TL,...
2
by: lbbs | last post by:
every time I open my table and want to print I have to go to printer setup to chance it to landscape and to change the margin size. Can you save those settings?
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
1
by: bughunter | last post by:
simple query select * from "Result" res where (res."QID" = 51541 or res."QID" = 51542) works fine ("SRV-BL"."Result" ~ 900000 rows) and returns 36 rows but update - no! update...
14
by: lewindletter | last post by:
Hi If I do Begin transaction Stmt 1: select count(*) from tableA If the count is zero, then proceed to the following Stmt 2: insert "something" to tableA Stmt 3: insert "something" to...
13
by: PinkBishop | last post by:
I am using VS 2005 with a formview control trying to insert a record to my access db. The data is submitted to the main table no problem, but I need to carry the catID to the bridge table...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.