Question: I have a table where I want to
exclude all rows where my large_text column contains a set of
keywords:
<field1> zzzz <status> movie tie
in<status>
The embedded columns values are as follows:
<exclude>media tie-in</exclude>
<exclude>movie tie-in</exclude>
<exclude>scholastic
tie-in</exclude>
Who do I write a SQL statement to
look inside the large_text column and not show the rows that
contain these columns?
Answer: Normally you can use the "not
like" clause or the more powerful
regexp_like which which can look
for patterns of text and numbers. For BLOB and CLOB datatypes,
use the
dbms_lob package to find and exclude embedded strings within
large objects.
You might be tempted to exclude the embedded row text based on a
common string:
select
text_column
where
text_column not like
('%tie-in%');
But you might get a false strike a missed row that has "tie in"
in another place in the text. In your case, with literal
values to exclude, you SQL might look like this:
select
text_column
where
text_column not like ('%media
tie-in%')
or
text_column not like ('%movie
tie-in%')
or
text_column not like
('%scholastic tie-in%');
Even above the SQL to
bypass (exclude rows) that contain your embedded literal may
accidently not bypass an y "tie on_ rows that we not embedded within
a <status> tag.
Also be aware that this type of query to eliminate unwanted rows
with embedded text values might cause a full table scan.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
