Re: Searching a LONG field

xkmd_at_uxa.ecn.bgu.edu (Kim Butler) writes:

> Using ORACLE Version 6, is it possible to search a database for all
> records which have a specified word in a LONG or CHAR(255) field?

For a CHAR(255) it's no problem. Just use the LIKE operator with wildcards. Example:

SELECT	TEXT
FROM	MYTABLE
WHERE	TEXT LIKE '%oracle%' ;	-- TEXT values containing the string "oracle"

Note though that a leading wildcard will disable the use of any index which might exist on the column being searched and force a full table scan. If you have heavy-duty text processing requirements you might want to look at Oracle's own product (which I think is called SQL*TextRetrieval; I have no experience of it).

Values of type LONG are not allowed in SQL expressions. If the column TEXT in the above example were LONG rather than CHAR(255) then you would have to resort to a 3GL to do the search (use a cursor to retrieve each row, store the LONG value in a host variable, and use the host language's facilities to examine the contents of the variable).

Hope this helps.

David Hazledine                                                EMBL Data Library
Database Administrator                                                PF 10.2209
EMBL Data Library                                       6900 Heidelberg, Germany

Internet: Hazledine_at_EMBL-Heidelberg.DE