File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and the fly likes Comparing NUMBER columns with String values Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Comparing NUMBER columns with String values" Watch "Comparing NUMBER columns with String values" New topic

Comparing NUMBER columns with String values

Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

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.

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?

The future is here. It's just not evenly distributed yet. - William Gibson
Consultant @ Xebia. Sonny Gill Tweets
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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.

Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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.

Jeanne Boyarsky
internet detective

Joined: May 26, 2003
Posts: 29220

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.

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

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 ]
I agree. Here's the link:
subject: Comparing NUMBER columns with String values
Similar Threads
Strange Behavior of Hibernate - NOT RESPONDING
fetch the records from cache
Pooling Prepared Statements - Good Idea?
How to retrieve the first record in a database table
Problem with ContainsKey(obj key) in HashMap