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 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.
"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.
"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
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
"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...
"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
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
"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... This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
by: Michael |
last post by:
I have a table that has the following fields:
tblECHECK
ID (autonumber identity column)
PTID
Batchnum
Page
DataPoint
DPValue
|
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,...
|
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?
|
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 ...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |