Re: is LIKE statement limited?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 17 Jan 2000 08:35:25 -0500
Message-ID: <m7668scv8aulq53d7tds56jt6rigja3j6p@4ax.com>

A copy of this was sent to dbrah_at_my-deja.com (if that email address didn't require changing) On Sun, 16 Jan 2000 02:57:03 GMT, you wrote:

>I have a big SELECT statement with many LIKE commands and it works fine
>if I only use 5 LIKE statements). Any more than 5 and it doesn't return
>anything. The SELECT is built dynamically from a web form using Perl and
>DBI/DBD::Oracle. I'm using Oracle 8i.
>
>SELECT * FROM filearchive WHERE (company LIKE '%%') AND (source LIKE
>'%%') AND (region LIKE '%%') AND (solution LIKE '%%') AND (technology
>LIKE '%%') AND (subject LIKE '%%') AND (summary LIKE '%%') AND (author
>LIKE '%%') AND (filetype LIKE '%%')
>
>All these fields (except author & filetype) can contain multiple terms
>separated by spaces so this was the only way I can figure out to create
>one SELECT to work for all cases. The web form I have allows users to
>select keywords from lists of categories and return a list of matching
>files. If a category is not chosen, LIKE '%%' is used because it acts
>as a simple placeholder or "don't care" which works great.
>
>This statement works fine (it has just 5 LIKE calls):
>
>SELECT * FROM filearchive WHERE (region LIKE '%%') AND (solution LIKE
>'%%') AND (summary LIKE '%%') AND (author LIKE '%%') AND (filetype LIKE
>'%Excel%')
>
>Is this a limitation of Oracle 8i? Is there a better way to do what I
>need to do?
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

No, it is not a limit. I'll betcha the 6'th column you add in is NULL and that is the cause of the 'missing' data. For example:

ops$tkyte_at_8i> create table emp as select * from scott.emp 2 /

Table created.

ops$tkyte_at_8i> alter table emp add category varchar2(30) 2 /

Table altered.

ops$tkyte_at_8i>
ops$tkyte_at_8i> select * from emp
  2
ops$tkyte_at_8i> select ename
  2 from emp
  3 where empno like '%%' and ename like '%%'

  4     and job like '%%' and hiredate like '%%'
  5     and sal like '%%' and deptno like '%%'

6 /

ENAME

SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER 14 rows selected.

ops$tkyte_at_8i> 
ops$tkyte_at_8i> 
ops$tkyte_at_8i> select ename

2 from emp
3 where empno like '%%' and ename like '%%'

  4     and job like '%%' and hiredate like '%%'
  5     and sal like '%%' and deptno like '%%'
  6     AND CATEGORY LIKE '%%'

7 /

no rows selected

When we query on the null column category -- no rows. If this is the case and you can live with the poor overall performance of a query like this (if your table has more then a small number of rows, this won't perform very well in the long run) you can code:

ops$tkyte_at_8i> select ename
2 from emp
3 where empno like '%%' and ename like '%%'

  4     and job like '%%' and hiredate like '%%'
  5     and sal like '%%' and deptno like '%%'
  6     AND (CATEGORY LIKE '%%' or CATEGORY is NULL)

7 /

ENAME

SMITH
...
FORD
MILLER 14 rows selected.

or:

ops$tkyte_at_8i> select ename
2 from emp
3 where empno like '%%' and ename like '%%'

  4     and job like '%%' and hiredate like '%%'
  5     and sal like '%%' and deptno like '%%'
  6     AND nvl(CATEGORY,'some value category can never have') LIKE '%%'

7 /

ENAME

SMITH
...
FORD
MILLER 14 rows selected.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jan 17 2000 - 07:35:25 CST