Question about the SQL "IN" Operator 1

[ASPTard]

I've got a series of filters that I would like to optimize and was wondering if anyone knows of a way to do this.

The syntax I'm using is for MSSQL/MS Access.

Here's the scenerio: I've got a database of ~5000 manufacturers that I would like to break down into groups based on the first character of the manufacturers name (i.e. '0-9', 'A-G', 'H-M', 'N-S', and 'T-Z')

The method I'm currently using works fine but I'd like to improve it if I can. Here is one of the current filters:

SELECT * FROM tblManufacturers WHERE IsActive=TRUE AND Name LIKE '0%' OR Name LIKE '1%' OR Name LIKE '2%' OR Name LIKE '3%' OR Name LIKE '4%' OR Name LIKE '5%' OR Name LIKE '6%' OR Name LIKE '7%' OR Name LIKE '8%' OR Name LIKE '9%' ORDER BY Name ASC

From what I've read about the IN Operator, it seems like it should do the trick; I'm afraid I just haven't found the right combination.

Here's what I've tried so far:

SELECT * FROM tblManufacturers WHERE IsActive=TRUE AND Name IN LIKE ('0%', '1%', '2%', '3%', '4%', '5%', '6%', '7%', '8%', '9%') ORDER BY Name ASC

SELECT * FROM tblManufacturers WHERE IsActive=TRUE AND Name LIKE IN ('0%', '1%', '2%', '3%', '4%', '5%', '6%', '7%', '8%', '9%') ORDER BY Name ASC

SELECT * FROM tblManufacturers WHERE IsActive=TRUE AND Name IN (LIKE '0%', LIKE '1%', LIKE '2%', LIKE '3%', LIKE '4%', LIKE '5%', LIKE '6%', LIKE '7%', LIKE '8%', LIKE '9%') ORDER BY Name ASC

SELECT * FROM tblManufacturers WHERE IsActive=TRUE AND Name IN ('0%', '1%', '2%', '3%', '4%', '5%', '6%', '7%', '8%', '9%') ORDER BY Name ASC

The first 3 statements resulted in "Missing Operator" errors and the final statement resulted in 0 (zero) records; but no error.

Can anyone think of other options? Or maybe another operator that will efficiently produce the results I'm looking for; without having the cumbersome select statement I currently have?

Thanks!!

ASPTard

 

[bborissov]

SELECT *
       FROM tblManufacturers
WHERE IsActive=TRUE AND
       Name LIKE '[0-9]%'
ORDER BY Name ASC

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[r937]

WHERE LEFT(name,1) BETWEEN '0' and '9'



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ASPTard]

Outstanding!! Thanks bborissov!! That worked perfectly!

 

[ASPTard]

That's another excellent suggestion r937, I'll keep that in mind. What I was looking for, however, was what bborissov had provided.

Thanks again!!

ASPTard

 

[r937]

how was i supposed to know that was what you were looking for?

and if you knew that was what you were looking for, why did you have to look for it?

besides, you did ask for "any other options"





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

The only disadvantage of r937 method is that the query will be not optimizable. On the other hand LIKE operator if you search from the beginning of the string is optimizable.
Of course only if you have index based on Name.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Exactly. Borislav's code is optimized and left(field,1) is not.

 

[ASPTard]

My apologies if I offended you r937. That's not what I trying to do. To answer your question, I have to ask you this: How can you look for something if you don't know what you're looking for?

I actually knew what I was looking for, I just didn't remember what it looked like.

Again, I appreciate your efforts and as previously stated "I will keep it in mind".

Thanks again!!

ASPTard