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 return. 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
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
posted
0
a sample would be:
select x.uname,(select count(*) from table2 where id like ( '%'||substr(x.uid,instr(x.uid,'#',1,1)) )) 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 ( '%'||'#XYZ' )) 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.
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.
0
