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 A010 and A025, I want to return A0101, A0200 and A0259.

What I've tried:

  1. Obviously, I cannot use WHERE myText BETWEEN @from and @to, because that won't return A0259.

  2. Technically, WHERE LEFT(myText, @len) BETWEEN @from AND @to, would be exactly what I want, but that kills SARGability.

  3. I could use WHERE myText BETWEEN @from and @to + 'zzzzzzzzzzz', but that is an ugly hack and potentially error-prone. (Is z really the highest character? Did I use enough "padding" characters?)

  4. 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.

  • 2
    I wonder whether 'A02' should be returned or not. Or 'A01'. Or 'A01F' or 'A010F' Commented Mar 21, 2016 at 12:39
  • @ypercubeᵀᴹ: Undefined, undefined, yes, yes.
    – Heinzi
    Commented Mar 21, 2016 at 15:29

1 Answer 1


If you use the >= and < operators you can return the required range by "increasing the second argument" by 1. Technically, replacing the last character ('5') with the next character ('6') in the character set.

Below is a query that returns all the rows in your range of A010 and A025. To get this I pass 'A026' as the second argument. The expression < 'A026' includes the value 'A0259456546'.

    Val NVARCHAR(100)
) ;

VALUES ('A0004534543'), 
       ('A0264565464') ;


FROM    @Test

WHERE   Val >= 'A010' 
        AND Val < 'A026' ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.