How do you select using a range of strings in SQL?

Question

I have a table of vehicles with registration numbers, and want to select a subset of them that are between some user-supplied 'from' and 'to' values.

So lets say the table looks like this:

id       reg_num
1        DD1111
2        DD1112
3        DE2245
4        EE5678
5        EF6547

The SQL I have so far looks like this:

select *
from vehicles
where reg_num >= 'DD'   -- this value is user supplied
and reg_num <= 'DE'     -- and so is this one

Which should (by my thinking) return:

1        DD1111
2        DD1112
3        DE2245

But instead, only returns:

1        DD1111
2        DD1112

I imagine that SQL server sees 'DE2245' as greater than 'DE', and so excludes the row.

My question: How do I get SQL server to include all rows that start with 'DE'?

Answer 1

You have to add 'zzzz's at the end as many as necessary to match your column width definition.

 select * from vehicles 
 where reg_num  >= 'DD' and reg_num <= 'DE' + 'ZZZZZZZZZZZZ'

Answer 2

where reg_num >= @userValueFrom 
and left(reg_num,char_length(@userValueTo) <= @userValueTo

but please note that this where does not utilize any index because of a function on the column in SARG.

Answer 3

If the format is guaranteed, you can simply do:

SELECT *
FROM vehicles 
WHERE LEFT(reg_num, 2) BETWEEN 'DD' AND 'DE'

But again, this is supposedly not SARGable - which always baffles me, because surely an index on reg_num can be used...