NOT IN and LIKE? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NOT IN and LIKE?

Author

Topic

jvegastn
Starting Member

23 Posts

Posted - 2009-06-26 : 11:32:04

I'm tinkering with SQL and I've run into a small problem. I'm trying to use exclude certain criteria using the NOT IN statement, but I'd also like to be able to use the LIKE because I do have some wildcards. I'm very new to this and I'm missing something. As to say

SELECT tbl.tblfield
FROM tbl
WHERE tbl.field Not In (criteria)

Thanks in advance.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-26 : 11:38:03

you can use

SELECT tbl.tblfield
FROM tbl
WHERE tbl.field Not like '%criteria1%' and tbl.field Not like '%criteria2%'

jvegastn
Starting Member

23 Posts

Posted - 2009-06-26 : 11:40:45

I thought about that; however, I have a lengthy bit of not like criteria, instead of running several dozen NOT LIKE I was hoping to use NOT IN, LIKE.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-06-26 : 11:57:25

[code]where not in (select field from tbl where field like '%criteria%' or field like '%criteria2%'...)[/code]

or perhaps a regular expression

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

jvegastn
Starting Member

23 Posts

Posted - 2009-06-26 : 12:11:54

Still run into a huge statement of ORs >.<

I'm looking around online and seeing LIKE IN statements but nothing as far as LIKE NOT IN, thinking I might be able to tuck a LIKE IN into a NOT IN maybe?


SELECT tbl.tblfield
from tbl 
WHERE tbl.tblfield NOT IN
(SELECT tbl.tblfield LIKE IN ("DP9%", "DP7%", "DI9%", etc.)

jvegastn
Starting Member

23 Posts

Posted - 2009-06-26 : 12:23:00

Even doctored up I'm still getting an error.


SELECT tbl.tblfield
FROM tbl
WHERE tbl.tblfield NOT IN
(SELECT tbl.tblfield from tbl
WHERE tbl.tblfield LIKE IN ("DP9%", "DP7%", "DI9%"));

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-06-26 : 12:29:03

Never seen nor heard of LIKE IN

Are you using MS SQL SERVER?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

jvegastn
Starting Member

23 Posts

Posted - 2009-06-26 : 12:39:14

Not so much, atm I'm just trying to write an access application using tables. At some point later down the line I'll link to production data.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-06-26 : 12:57:43

You could try something like:

SELECT T.tblfield
FROM tbl T
    LEFT JOIN
    (
        SELECT 'DP9' UNION ALL
        SELECT 'DP7' UNION ALL
        SELECT 'DI9'
    ) D (Item)
        ON T.tblfield LIKE D.Item + '%'
WHERE D.Item IS NULL

SELECT T.tblfield
FROM tbl T
WHERE NOT EXISTS
(
    SELECT *
    FROM
    (
        SELECT 'DP9' UNION ALL
        SELECT 'DP7' UNION ALL
        SELECT 'DI9'
    ) D (Item)
    WHERE T.tblfield LIKE D.Item + '%'
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 13:06:38

quote:

Originally posted by Ifor

SELECT T.tblfield
FROM tbl T
    LEFT JOIN
    (
        SELECT 'DP9' UNION ALL
        SELECT 'DP7' UNION ALL
        SELECT 'DI9'
    ) D (Item)
        ON T.tblfield LIKE D.Item + '%'
WHERE D.Item IS NULL

A like case like this will cause a scan and will be slow.
However, if the LIKE cases are always 3 characters long, try this becuase the query engine is now available to do a HASH JOIN.

SELECT		t.tblfield
FROM		tbl AS t
LEFT JOIN	(
			SELECT 'DP9' UNION ALL
			SELECT 'DP7' UNION ALL
			SELECT 'DI9'
		) AS d(Item) ON d.Item = SUBSTRING(t.tblfield, 1, 3)
WHERE		d.Item IS NULL

E 12°55'05.63"
N 56°04'39.26"

jvegastn
Starting Member

23 Posts

Posted - 2009-06-26 : 13:34:50

I wish it could be that easy. Unfortunately the LIKE instances will range in character length from 3 chars to 5 or more.

jvegastn
Starting Member

23 Posts

Posted - 2009-06-26 : 13:40:44

The exemptions are fairly static. Would it make more sense to create an additional table of all exclusions and use query against the table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 13:53:29

quote:
Originally posted by jvegastn

The exemptions are fairly static. Would it make more sense to create an additional table of all exclusions and use query against the table.

even then if you're looking at wildcard on condition using like then what difference will it make?

jvegastn
Starting Member

23 Posts

Posted - 2009-06-26 : 13:58:40

In using wildcards i was only looking to cut down on the lengthy OR statements as there is enough criteria that I might eventually hit the SQL statement limit size. If I was running it against a table I could fill in all the exemptions and not have to look towards wildcards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 14:01:55

quote:
Originally posted by jvegastn

In using wildcards i was only looking to cut down on the lengthy OR statements as there is enough criteria that I might eventually hit the SQL statement limit size. If I was running it against a table I could fill in all the exemptions and not have to look towards wildcards.

yup. that sounds better. also you could add an index on new table on join column and see if it helps

jvegastn
Starting Member

23 Posts

Posted - 2009-06-26 : 14:05:27

That would lead me to this question. It woulld seem this would call for a JOIN statement except I only want to return records from the one table AND I want it to filter the records out rather than return them.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 14:08:00

it would do the same in effect. in select list you will give only the fields of first table but join will make sure you include only those values in second table

jvegastn
Starting Member

23 Posts

Posted - 2009-06-26 : 14:10:21

Brb with some code.

jvegastn
Starting Member

23 Posts

Posted - 2009-06-26 : 14:17:51

Ok so tblData is the inventory I need filtered, whereas, tblCriteria will be the data I'm hoping to exclude.

SELECT tblData.ItemNumber, *
FROM tblData LEFT OUTER JOIN tblCriteria ON tblData.ItemNumber = tblCriteria
WHERE tblData.ItemNumber Is Null

Looking anything like it supposed to?

jvegastn
Starting Member

23 Posts

Posted - 2009-06-26 : 14:26:39

My syntax is all screwy and apparently I have to have a full filepath?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-06-26 : 14:31:10

Follow the first link in my signature, and follow the instructions. This will give the brainy guys some actual tables and data to work with, and aid them in supplying a solution.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources