Let's say I want all records with a prefix between two user-supplied alphanumeric values of the same length. Thus, if the user enters
A025, I want to return
What I've tried:
Obviously, I cannot use
WHERE myText BETWEEN @from and @to, because that won't return
WHERE LEFT(myText, @len) BETWEEN @from AND @to, would be exactly what I want, but that kills SARGability.
I could use
WHERE myText BETWEEN @from and @to + 'zzzzzzzzzzz', but that is an ugly hack and potentially error-prone. (Is
zreally the highest character? Did I use enough "padding" characters?)
I could "explode" the range and search for every possible prefix, e.g.
WHERE (myText LIKE 'A01%' OR myText LIKE 'A020%' OR myText LIKE 'A021%' ...), but that's a lot of work.
Is there some smart solution that I've missed? I'll probably use option 3 to solve the problem (since I know about the length and the range of allowed characters), but I'm curious about the general case.