SQL Server: list items between AA and AM

I'm looking t开发者_JAVA百科o list all rows where the field starts between the range of AA to AM. I know I could to this by ORing a ton of LIKEs together, but is there a better way?

I'm running SQL Server if that changes things.

Thanks!

Use LIKE and a range

LIKE 'A[A-M]%'

Case sensitivity depends on the collation of course

Use greater than & less than:

select * from table
where col > 'AA'
and col < 'AM'

This is not inclusive, if the value of col is 'AA' then it won't match, but it will match 'AAA'. Change to greater than or equal to to match 'AA' (inclusive). If you also want anything that starts with 'AM' add another or to include 'AM%'. These two changes to the query look like:

select * from table
where col >= 'AA'
and col < 'AM'
or col like 'AM%'

If you create a procedure that can handle variables, you'll be able to run the query over and over for different subsets of the table (e.g., maybe next time you run it, you want all records between 'DA' and 'FZ')

Also -- avoid wildcards when you can. SELECT *... is not generally a good idea (you should list the field names instead), and also I replaced the other wildcard (%) in the WHERE clause with the LEFT function in stead.

 CREATE PROCEDURE dbo.GETdata
    ( @first nvarchar(2), @last nvarchar(2) )
    AS
    BEGIN
    SET NOCOUNT ON;

    SELECT
       field1,
       field2,
       ...
    FROM 
       dbo.Table 
    WHERE
           LEFT(field1,2) >= @first
       AND LEFT(field1,2) <= @last

    END

Then to run the stored procedure:

EXECUTE dbo.GETdata 'AA', 'AM'

varchar:

where field between 'AA' and 'AM' + CHAR(255)

nvarchar:

where field between N'AA' and N'AM' + NCHAR(65535)