This week's book giveaway is in the Big Data forum.
We're giving away four copies of Elasticsearch in Action and have Radu Gheorghe & Matthew Lee Hinman on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes how to use variable with oracle like Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Elasticsearch in Action this week in the Big Data forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "how to use variable with oracle like" Watch "how to use variable with oracle like" New topic

how to use variable with oracle like

Ashish Shinde
Ranch Hand

Joined: Dec 08, 2002
Posts: 34

I need to use a substring while doing a slect which involves a like statment.
Something like "select id from ....... where uid like '%'||<variable>.
where <variable> is a substring of some other column.

I tried using the above but it does not work since query just does not
There is no issue with query because if I insert hardcoded value expected from substring into the variable,
it returns the required result immediately.

Does anybody know how to go about this?using a variable in like clause
Carol Enderlin
Ranch Hand

Joined: Oct 10, 2000
Posts: 1364
That looks like pseudo-code, hard to comment. It would help if you would post a real sample, maybe you can come up with something really simple that you don't have to edit out for posting.

Just does not return? Do you mean it is slow?

Are you doing this in sql or in jdbc or?
[ January 31, 2007: Message edited by: Carol Enderlin ]
Ashish Shinde
Ranch Hand

Joined: Dec 08, 2002
Posts: 34
a sample would be:

select x.uname,(select count(*) from table2 where id like (
from table1 x

Here the expression "'%'||substr(x.uid,instr(x.uid,'#',1,1))" is variable in the like clause.However this does not work as expected.
When it is hardcoded as :
select x.uname,(select count(*) from table2 where id like (
from table1 x.
This works and returns the result.
The query takes a very long time i guess. I am executing this as a sql stmt and not jdbc.
Mark Spritzler

Joined: Feb 05, 2001
Posts: 17260

So you have a subquery.

How does the subquery know what "x" means?

I was just thinking about what happends if the two tables are in the FROM clause and there is a join on your subqueries where clause (Theta-style join) and then have your count() in the select, and a GROUP BY clause. Just an idea.


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Bear Bibeault
Author and ninkuma

Joined: Jan 10, 2002
Posts: 62127

"ash sh", you have previously been warned on one or more occasions regarding adjusting your display name to meet JavaRanch standards. This is not optional. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it prior to your next post.

Your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Be aware that accounts with invalid display names are removed.

JavaRanch Sheriff

[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
I agree. Here's the link:
subject: how to use variable with oracle like