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' – ypercubeᵀᴹ Mar 21 '16 at 12:39
  • @ypercubeᵀᴹ: Undefined, undefined, yes, yes. – Heinzi Mar 21 '16 at 15:29
up vote 7 down vote accepted

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

DECLARE @Test TABLE
(
    Val NVARCHAR(100)
) ;

INSERT @Test
VALUES ('A0004534543'), 
       ('A01034950834'), 
       ('A020043553'), 
       ('A0259456546'), 
       ('A0264565464') ;

SELECT  *

FROM    @Test

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

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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