Exclude Word List to filter SELECT results

Question

Post reply

Exclude Word List to filter SELECT results

Rob Levy

Mr or Mrs. 500

Points: 565
More actions
October 7, 2008 at 5:18 am

#194093

Hi all,
I have a table of locations. Some of the locations are of no interest to me (but I don't control the contents of this table)
I would like to filter this list of locations using certain key words, stored in a table.
Let's take an example:
Location
--------
MyHouse
YourHouse
MyGarden
Exclude Table
-------------
Your
Result
------
MyHouse
MyGarden
Can anyone think of a neat way of doing this?
Thanks,
Rob

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

Sure... the criteria to add to your query would be ...

WHERE yada-yada

AND Location NOT LIKE (SELECT '%'+Word+'%' dbo.FROM ExcludeTable)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rob Levy Mr or Mrs. 500 Points: 565 More actions · Answer 2

Great! Didn't know you could do that.

Thanks a lot for the super-fast response too!

Rob

Rob Levy Mr or Mrs. 500 Points: 565 More actions · Answer 3

Hi again all,

I get

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.

when I try and run Jeff's suggestion on SQL Server 2005.

I'm clearly getting something wrong here, I just can't work out what it is! Is the

LIKE (SELECT ...)

syntax that Jeff suggests supported on my version of SQL Server?

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

Ah crud... my fault... that's what I get for posting without being under the influence of coffee.

Lemme see what I can do about this... haven't had to do it in a very long time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rob Levy Mr or Mrs. 500 Points: 565 More actions · Answer 5

Rob Levy

Mr or Mrs. 500

Points: 565

October 8, 2008 at 3:16 am

#882143

🙂

sgmunson SSC Guru Points: 110639 More actions · Answer 6

Here ya go:

DECLARE @LOCATION_TABLE TABLE(

Location varchar(15)

PRIMARY KEY(Location)

)

INSERT INTO @LOCATION_TABLE

SELECT 'MyHouse' AS Location UNION ALL

SELECT 'YourHouse' UNION ALL

SELECT 'MyGarden'

DECLARE @EXCLUDE_TABLE TABLE(

Word varchar(15)

PRIMARY KEY(Word)

)

INSERT INTO @EXCLUDE_TABLE

SELECT 'Your' AS Word

;WITH NOT_WANTED AS (

SELECT Location

FROM @LOCATION_TABLE, @EXCLUDE_TABLE

WHERE Location LIKE '%'+Word+'%'

)

SELECT *

FROM @LOCATION_TABLE

WHERE Location NOT IN (SELECT Location FROM NOT_WANTED)

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Rob Levy Mr or Mrs. 500 Points: 565 More actions · Answer 7

Amazing!

smunson's response features two T-SQL functionalities I've never seen before (That's a lot for a single post!)

The ;WITH construct to make a Common Table Expression (CTE) is a really interesting option to avoid repeating lengthy subqueries

(I read all about it here:http://msdn.microsoft.com/en-us/magazine/cc163346.aspx).

I also have never seen the SELECT * FROM @TABLE_VARIABLE1, @TABLE_VARIABLE2 which results in

LOCATION | WORD

-----------+-----

MyGarden | Your

MyHouse | Your

YourGarden | Your

(i.e. a FULL OUTER JOIN of the two tables)

All very interesting (and a great solution!).

Many thanks to smunson for this!

sgmunson SSC Guru Points: 110639 More actions · Answer 8

Thanks for the kind words - glad I could help.

Something to keep in mind, however, is that in using a CROSS JOIN (see next paragraph), you get a cartesian product kind of result, which can cause serious performance concerns if the number of records in each table gets very big, as the number of inital result rows before the WHERE clause limits the resultset is the product of the number of records in each table.

My query wasn't exactly the same as a FULL OUTER JOIN, as there was no JOIN keyword and no ON clause, and it used a WHERE clause to limit the resultset instead. If I remember correctly, at some point they may deprecate the lack of a JOIN keyword, so the "comma separator" for tables may not remain valid in future SQL versions. Getting the same results and using a JOIN keyword would be more accurately depicted in T-SQL using CROSS JOIN than FULL OUTER JOIN, but the results could be made to be the same with an appropriate JOIN condition.

So... this solution works well in this case, but might not work best if there were a significantly larger number of records in each table.

Steve

(aka smunson)

:):):)

Rob Levy (10/9/2008)
Amazing!
smunson's response features two T-SQL functionalities I've never seen before (That's a lot for a single post!)
The ;WITH construct to make a Common Table Expression (CTE) is a really interesting option to avoid repeating lengthy subqueries
(I read all about it here:http://msdn.microsoft.com/en-us/magazine/cc163346.aspx).
I also have never seen the SELECT * FROM @TABLE_VARIABLE1, @TABLE_VARIABLE2 which results in
LOCATION | WORD
-----------+-----
MyGarden | Your
MyHouse | Your
YourGarden | Your
(i.e. a FULL OUTER JOIN of the two tables)
All very interesting (and a great solution!).
Many thanks to smunson for this!

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Here's another (simple) way to do it:

Select *

From Locations

Where NOT EXISTS( Select * From Excludes

Where Location LIKE '%'+Excludes+'%')

This is probably close to what Jeff intended to write, had he been properly caffeinated. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 10

rbarryyoung (10/9/2008)
Here's another (simple) way to do it:
Select *
From Locations
Where NOT EXISTS( Select * From Excludes
Where Location LIKE '%'+Excludes+'%')
This is probably close to what Jeff intended to write, had he been properly caffeinated. 🙂

Yeah... that's the ticket. I lost track of this post... was busy feeding myself pork chops for having posted without testing. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Jeff Moden (10/9/2008)
Yeah... that's the ticket. I lost track of this post... was busy feeding myself pork chops for having posted without testing. 🙂

That's my favorite cure! 😛

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 12

I know this is a 2005 thread, but here is a simple solution that will work anywhere. I'm a big fan of the KISS principle. 🙂 (I'll just shamelessly borrow smunson's code for creating and loading the tables.)

DECLARE @LOCATION_TABLE TABLE(

Location varchar(15)

PRIMARY KEY(Location)

)

INSERT INTO @LOCATION_TABLE

SELECT 'MyHouse' AS Location UNION ALL

SELECT 'YourHouse' UNION ALL

SELECT 'MyGarden'

DECLARE @EXCLUDE_TABLE TABLE(

Word varchar(15)

PRIMARY KEY(Word)

)

INSERT INTO @EXCLUDE_TABLE

SELECT 'Your' AS Word

-- Ok, now my stuff

select l.*

from @LOCATION_TABLE l

left join @EXCLUDE_TABLE e

on l.Location like '%' + e.Word + '%'

where e.word is null;

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Tomm: That doesn't do the same thing. Note that if some EXCLUDEs match a LOCATION row and some do not, then it will return one or more multiples of the LOCATION row, though it should return none.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 14

rbarryyoung (10/10/2008)
Tomm: That doesn't do the same thing. Note that if some EXCLUDEs match a LOCATION row and some do not, then it will return one or more multiples of the LOCATION row, though it should return none.

Not sure what you're talking about... it looks alright to me...

DECLARE @LOCATION_TABLE TABLE(

Location varchar(15)

PRIMARY KEY(Location)

)

INSERT INTO @LOCATION_TABLE

SELECT 'MyHouse' AS Location UNION ALL

SELECT 'YourHouse' UNION ALL

SELECT 'YourPlace' UNION ALL

SELECT 'YourPad' UNION ALL

SELECT 'MyGarden' UNION ALL

SELECT 'OurPlace' UNION ALL

SELECT 'OurGarden' UNION ALL

SELECT 'HisHouse' UNION ALL

SELECT 'HerHouse'

DECLARE @EXCLUDE_TABLE TABLE(

Word varchar(15)

PRIMARY KEY(Word)

)

INSERT INTO @EXCLUDE_TABLE

SELECT 'Your' UNION

SELECT 'Our'

-- Ok, now my stuff

select l.*

from @LOCATION_TABLE l

left join @EXCLUDE_TABLE e

on l.Location like '%' + e.Word + '%'

where e.word is null;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)