SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using NOT LIKE with multiple values		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sonjan
Starting Member

Australia
21 Posts
Posted - 01/17/2013 :  19:50:02  Show Profile  Reply with Quote
Hi

I am trying to write a script to retrieve all fuel transactions that are not in multiple locations.

Example:

select * from fueltrans with (nolock) where location not like ('%capalaba%','%ormiston%','%thorneside%')

Appreciate feedback.
Thanks
Sonja

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 01/17/2013 :  19:59:24  Show Profile  Reply with Quote
Depends on whether you want not to have all 3 or either one
.If either one you can change it to OR

Select * from fueltrans with (nolock) Where (location not like '%capalaba%') AND (location not like '%ormiston%') AND (location not like '%thorneside%')
Edited by - sodeep on 01/17/2013 20:00:21
Go to Top of Page

sonjan
Starting Member

Australia
21 Posts
Posted - 01/17/2013 :  20:02:53  Show Profile  Reply with Quote
Thank you, it was to include all suburbs listed.
Sonja
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47188 Posts
Posted - 01/17/2013 :  22:34:25  Show Profile  Reply with Quote 

select f.* from fueltrans f
join (select 'capalaba' as cat
      union all
      select 'ormiston'
      union all
      select 'thorneside'
      )t
on f.location not like '%' + t.cat + '%'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Edited by - visakh16 on 01/17/2013 22:34:39
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1773 Posts
Posted - 01/18/2013 :  12:55:47  Show Profile  Visit jackv's Homepage  Reply with Quote
Using NOT LIKE will force a scan through all possibilities.There are probably more efficient ways.
You're using NOLOCK in your initial query. If you're also issuing updates , inserts this could cause dirty reads. Is that intended behaviour?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47188 Posts
Posted - 01/18/2013 :  13:17:39  Show Profile  Reply with Quote
see

http://visakhm.blogspot.in/2010/02/avoiding-deadlocks-using-new.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1773 Posts
Posted - 01/19/2013 :  03:27:48  Show Profile  Visit jackv's Homepage  Reply with Quote
keep in mind if you're to implement READ_COMMITTED_SNAPSHOT isolation level there is added pressure on the tempdb. Tempdb can potentially be a serious bottleneck.Of course , it is possible to tune the tempdb , such as the type of storage , file numbers etc
http://www.sqlserver-dba.com/2011/04/tempdb-performance-and-strategy-checklist.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000