not like use with and or - 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

SQL Server 2000 Forums

Transact-SQL (2000)

not like use with and or

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-18 : 08:56:54

Tina K writes "--I am using SQL Server 2000 with Windows 2K server

/* I am not getting correct results if I use not like along with and/or as shown on script 1.

I am trying to get item codes that doesn't start with G, 6 or R in my result set within a certain date range. But when I run below sample, all the item codes gets included in my result set, including those that start with G, 6, and R. */

--1
select table1.item_code
from table1
join table2
on table1.column1 = table2.column1
where table1.transaction_date between @startdate and @enddate
and (table1.item_code not like 'G%'
or table1.item_code not like '6%'
or table1.item_code not like 'R%')

/*If I run below sample, I only get item codes that starts with G, 6, or R. */
--2
select table1.item_code
from table1
join table2
on table1.column1 = table2.column1
where table1.transaction_date between @startdate and @enddate
and (table1.item_code like 'G%'
or table1.item_code like '6%'
or table1.item_code like 'R%')

/*Since like gives me the correct results, why not like is not giving correct results when used with or? Is there a problem in my first script that it's not giving expected results? */"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-18 : 09:02:34

in query 1
use and instead of or

--------------------
keeping it simple...

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2006-05-18 : 09:10:36

select table1.item_code
from table1
join table2
on table1.column1 = table2.column1
where table1.transaction_date between @startdate and @enddate
and (table1.item_code not like 'G%'
and table1.item_code not like '6%'
and table1.item_code not like 'R%')

Kapil Arya

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-18 : 11:45:23

Better yet, you can just use more pattern matching in the like clause...

--anything starting with G, 6 or R
select table1.item_code
from table1
join table2
on table1.column1 = table2.column1
where table1.transaction_date between @startdate and @enddate 
and table1.item_code like '[G6R]%'

--anything not starting with G, 6 or R
select table1.item_code
from table1
join table2
on table1.column1 = table2.column1
where table1.transaction_date between @startdate and @enddate 
and table1.item_code like '[^G6R]%'

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources