Excluding Records, Want to exclude records that contain specific text Options

[ilundy77]

hi,

I'm very new to sql. I'm using rapid sql to query an oracle db. I need to exclude records that contain the following text:

• reissue
• scholastic
• media tie-in
• movie tie-in

Currently, I'm using the following query, which returns records that include the text I'd like to exclude (reissue, scholastic, media tie-in, movie tie-in).

Select
ISBN,
BOOK_TITLE,
PUBLISHER_DESCRIPTION,
AUTHOR_BYLINE,
PRODUCT,
COVER_STATUS,
ASSET_TYPE

from ts_admin.editorial
where season in ('2011 September',
'2011 October',
'2011 November',
'2011 December',
'2012 January',
'2012 February',
'2012 March',
'2011 Fall/ 2012 Winter')
and asset_type = 'Cover Image'

Can anyone help me edit this query so that it excludes records containing the text that I've indicated? Thanks in advance for the help. I greatly appreciate it.

[dvsoukup]

So that text that you want to exclude....is it in a specific field? Can't tell where it would be merely by looking at your query there

Several options here. You could use UNIONS and remove each record w/ that text from each of the queries in the different UNIONS.
ie.
select * from ts_admin.editorial where <field> <> 'reissue'
union
select * from ts_admin.editorial where <field> <> 'scholastic'
union
select * from ts_admin.editorial where <field> <> 'media tie-in'
union
select * from ts_admin.editorial where <field> <> 'movie tie-in'



Or you could probably do something with a nested sub-query like so:

where <field> not in (select <field> from ts_admin.editorial where <field2(field containing your text...)> = 'reissue'
or <field2> = 'scholastic'
or <field2> = 'media tie-in'
or <field2> = 'movie tie-in')


Easiest option would probably be to just incorporate the exclusions right into your query:
Select
ISBN,
BOOK_TITLE,
PUBLISHER_DESCRIPTION,
AUTHOR_BYLINE,
PRODUCT,
COVER_STATUS,
ASSET_TYPE

from ts_admin.editorial
where season in ('2011 September',
'2011 October',
'2011 November',
'2011 December',
'2012 January',
'2012 February',
'2012 March',
'2011 Fall/ 2012 Winter')
and asset_type = 'Cover Image'
and ([field1] <> 'reissue' OR [field1] <> 'scholastic' OR [field1] <> 'media tie-in' OR [field1] <> 'movie tie-in')


Now if those strings you are wanting to exclude are part of a larger string that you would need to parse out, you have a bit more complicated problem on your hand...though still do-able

Several ways to do it, but whichever you choose, choose the fastest one. Can run explain plans against the different queries to figure out which one is most optimal.

[ilundy77]

Thank you very much for your help, and for bearing with my newbie mistakes. And yes, the text would be in either the MISC or TITLE field. Sorry for the confusion.

So that text that you want to exclude....is it in a specific field? Can't tell where it would be merely by looking at your query there

Several options here. You could use UNIONS and remove each record w/ that text from each of the queries in the different UNIONS.
ie.
select * from ts_admin.editorial where <field> <> 'reissue'
union
select * from ts_admin.editorial where <field> <> 'scholastic'
union
select * from ts_admin.editorial where <field> <> 'media tie-in'
union
select * from ts_admin.editorial where <field> <> 'movie tie-in'



Or you could probably do something with a nested sub-query like so:

where <field> not in (select <field> from ts_admin.editorial where <field2(field containing your text...)> = 'reissue'
or <field2> = 'scholastic'
or <field2> = 'media tie-in'
or <field2> = 'movie tie-in')


Easiest option would probably be to just incorporate the exclusions right into your query:
Select
ISBN,
BOOK_TITLE,
PUBLISHER_DESCRIPTION,
AUTHOR_BYLINE,
PRODUCT,
COVER_STATUS,
ASSET_TYPE

from ts_admin.editorial
where season in ('2011 September',
'2011 October',
'2011 November',
'2011 December',
'2012 January',
'2012 February',
'2012 March',
'2011 Fall/ 2012 Winter')
and asset_type = 'Cover Image'
and ([field1] <> 'reissue' OR [field1] <> 'scholastic' OR [field1] <> 'media tie-in' OR [field1] <> 'movie tie-in')


Now if those strings you are wanting to exclude are part of a larger string that you would need to parse out, you have a bit more complicated problem on your hand...though still do-able

Several ways to do it, but whichever you choose, choose the fastest one. Can run explain plans against the different queries to figure out which one is most optimal.

[burleson]

Hi

>> I need to exclude records that contain the following text:

Or, you mightlike to filter our unwanted rows)

CODE

select
  tezt_column
where
text_column not in ('media tie-in,'scholastic','ETC')

[ilundy77]

Hi

>> I need to exclude records that contain the following text:

Or, you mightlike to filter our unwanted rows)

CODE

select
  tezt_column
where
text_column not in ('media tie-in,'scholastic','ETC')

Thanks very much!

[burleson]

Sorry!

If the column is embedded inside other fields, use "like" or regexp_like:

CODE

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%')
or
text_column not like ('&reissue&')

Please read:

http://www.dba-oracle.com/t_sql_exclude_ro...ded_strings.htm