• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Comparing NUMBER columns with String values

 
Sonny Gill
Ranch Hand
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I just realized that in Oracle, you can compare a NUMBER column with a String value.
For example, if there is a table emp with a column emp_no of type NUMBER(4),

You can do
SELECT name FROM emp WHERE empno=123 , no surprise there.

But you can also do
SELECT name FROM emp WHERE empno='123' or
SELECT name FROM emp WHERE empno=' 123 '
and this gets you the same results.

Although
SELECT name FROM emp WHERE empno=' 12 3' gives you an Oracle 'Invalid Number' error.

I am just curious if it is the same for other major databases, because sometimes it is convenient to call setString() for integer parameters, rather than parse it first, esp. when you can be pretty sure that the String being passed is a valid number.

Had anybody had any problems with this before?
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sonny,

IMHO relying on implicit type conversions is always a bad idea. From a performance point of view your code examples need to convert the supplied string to a number before comparing it to the column values. At worst they will convert the column value to a string for each row that needs to be compared (although it doesn't appear that way in this case). Supplying the right data type in the first place will eliminate the potential for unexpected database errors like the one in your example. It's also difficult for a programmer using your code in future to determine whether you did it accidentally or on purpose, i.e. is it a latent bug that's just working by accident?

That's my opinion anyway.

Jules
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, and behaviour in other major RDBMS varies. From memory (not 100% reliable!), INGRES would allow it; MS SQL Server, Sybase and MS Access would not; MySql ... errr ... might (unless you wanted to do it in a sub-query ) and I don't know DB2.

Jules
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34965
377
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DB2 does not allow you to do it. You would get a runtime error about invalid syntax.

You really should consider using a prepared statement. That way the driver will handle all the type conversion details.
 
Sonny Gill
Ranch Hand
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I was actually using a PreparedStatement.

Just to see what happens, I set the PreparedStatement parameter (which is being compared with a NUMBER column), using setString(), since I was getting the value as a HTTP request parameter, and I was surprised that it actually worked.

Thanks Julian, Jeanne. I realize it is a bad idea, the idea of not having to use parseInt, and try-catch for NumberFormatException was tempting though
[ August 09, 2004: Message edited by: Sonny Gill ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic