[dba-SQLServer] IN() or NOT IN()

You're right, I thought I had used it this way before, but it appears that I
was incorrect.  What I must have been thinking of was:

LIKE ('[4-9, A-M]')
LIKE ('[^1-3, N-T]')
NOT LIKE ('[1-3, N-T'])
NOT LIKE ('[^4-9, A-M]')

John, the ^ is a bit operator.

Ken

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Tuesday, May 15, 2007 8:04 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] IN() or NOT IN()

Are you sure? I've never seen anything that says that IN() takes 
anything other than a Subquery or a List of expressions.

The SQL Server 2000 that I have here returns:
"Invalid column name 4-9" if I try that.

On 15 May 2007 at 19:44, kens.programming wrote:

> You shouldn't have to use a comma limited list, just square brackets to
> designate your sets.
> 
> IN ([4-9], [A-M])
> IN ([^1-3], [^N-T])
> NOT IN ([1-3], [N-T])
> NOT IN ([^4-9], [^A-M])
> 
> Ken
> 
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Tuesday, May 15, 2007 7:34 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer] IN() or NOT IN()
> 
> I am trying to process a query where an income field has a set of possible
> values, 1-9 and A-T.  The client wants values 409 and A-M.  Logically that
> would be more efficient if it was NOT in(1-3,n-t).  Is it in fact more
> efficient?  And can ranges like that be specified or do I need to use
comma
> delimted lists 1,2,3,n,o,p...?
> 
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> No virus found in this incoming message.
> Checked by AVG Free Edition. 
> Version: 7.5.467 / Virus Database: 269.7.1/805 - Release Date: 5/15/2007
> 10:47 AM
>  
> 
> No virus found in this outgoing message.
> Checked by AVG Free Edition. 
> Version: 7.5.467 / Virus Database: 269.7.1/805 - Release Date: 5/15/2007
> 10:47 AM
>  
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.467 / Virus Database: 269.7.1/805 - Release Date: 5/15/2007
10:47 AM
 

No virus found in this outgoing message.
Checked by AVG Free Edition. 
Version: 7.5.467 / Virus Database: 269.7.1/805 - Release Date: 5/15/2007
10:47 AM