SQL Server: list items between AA and AM

Question

I'm looking to 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!

Answer 1

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%'

Answer 2

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'

Answer 3

Use LIKE and a range

LIKE 'A[A-M]%'

Case sensitivity depends on the collation of course

Answer 4

varchar:

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

nvarchar:

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