aspose file tools*
The moose likes JDBC and the fly likes How to use Like statement for int column? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to use Like statement for int column?" Watch "How to use Like statement for int column?" New topic
Author

How to use Like statement for int column?

Hrishikesh Ghatnekar
Greenhorn

Joined: Oct 25, 2004
Posts: 28
Hi ,

I have a table which contains two columns, Emp Number and Emp Name where Emp Number is int and Emp Name is String.

I want to run a search on the same table for which I am trying to run two quaries.

1)Select * from mytable where Emp_Name LIKE �%ri%�
2)Select * from mytable where Emp_Number LIKE �%3%�


Now my problem is the first quary runs fine , but second does not.
I am getting error ->
Function LIKE invoked with wrong number or type of argument(s).


My question is is it possible to use LIKE keyword for the columns which contain int?

If no then when is the other way to do this kind of quary?


Thanks
Hrishikesh
Aman Verma
Greenhorn

Joined: May 10, 2005
Posts: 15
Hi Hrikesh,

I think We can use % to search integers from a table.
e.g on oracle 9i following queries work fine and gives expected results.

SELECT * FROM test WHERE id LIKE '3%'

SELECT * FROM test WHERE id LIKE '%3'

SELECT * FROM test WHERE id LIKE '%3%'

These queries work fine on oracle 9i but I donno abt the previous versions of oracle.

Regards,
-Rahul
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

This question might be better posted in the JDBC forum, where SQL questions tend to get asked. You are more likely to get a response.

That being said:

My question is is it possible to use LIKE keyword for the columns which contain int?

No (unless the emp_number column is a character data type). The LIKE operator is for character, string or clob values, not numeric values. What is "like" 3 anyway? Values near it e.g. 2 and 4? Or values including a 3 e.g 33, 333, 1903, 0.23232?

You could change Emp_number to a varchar datatype, or use a BETWEEN or IN operator, if either of those suit what you are trying to acheive.
[ May 16, 2005: Message edited by: Paul Sturrock ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Ernest Friedman-Hill
author and iconoclast
Marshal

Joined: Jul 08, 2003
Posts: 24187
    
  34

Moving to JDBC.


[Jess in Action][AskingGoodQuestions]
Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

Depending on the database, you may use a builtin function, to convert empid to a string, and perform LIKE on that string:


Consult your DB-Manual, to find out, what's the real name for 'toString'.


http://home.arcor.de/hirnstrom/bewerbung
 
Don't get me started about those stupid light bulbs.
 
subject: How to use Like statement for int column?