RE: UNABLE TO RETRIEVE data while using between operator

SSC Guru

Points: 186127

February 5, 2014 at 2:23 am

The BETWEEN range ends at 'V'. If you have a vendor with exactly this value then it will be included. Vendors called 'V' + anything else will be excluded because they sort after 'V', outside your range. As an experiment, try BETWEEN 'N' AND 'W', which will return all the vendors beginning with 'V' and any vendors which are just 'W'.

There's another way to collect all the vendors beginning with 'V', and that's to follow it with a character which would sort after the letters, something like BETWEEN 'N' AND 'V'+CHAR(255).

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden