438,856 Members | 2,179 Online Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

# How to use BETWEEN with custom-ordered values

 P: n/a We have a 10 digit primary key value in this format: M000123456. The order for this key is first determined by positions 3 and 4 in this example, then positions 1 and 2. So a brief sample of correct ordering would look like this: M000001501 M000011501 M000021501 M000001601 M000011601 M000021601 Now my question: how can I use a BETWEEN (or > and <) in my WHERE clause to get a range of values for this column? I use the following ORDER BY clause to control how the results are sorted, but I can't get the same logic to work with BETWEEN in a WHERE clause. ORDER BY SUBSTRING(, 7, 2), SUBSTRING (, 5, 2) How do I return values between M000011501 and M000011601 for example? Jul 23 '05 #1
3 Replies

 P: n/a On 19 Dec 2004 16:36:14 -0800, ia*********@gmail.com wrote: We have a 10 digit primary key value in this format: M000123456. Theorder for this key is first determined by positions 3 and 4 in thisexample, then positions 1 and 2. So a brief sample of correct orderingwould look like this:M000001501M000011501M000021501M000001601M000011601M000021601Now my question: how can I use a BETWEEN (or > and <) in my WHEREclause to get a range of values for this column? I use the followingORDER BY clause to control how the results are sorted, but I can't getthe same logic to work with BETWEEN in a WHERE clause.ORDER BY SUBSTRING(, 7, 2), SUBSTRING (, 5, 2)How do I return values between M000011501 and M000011601 for example? Hi Ian, Usually, this kind of requirement is a sign of a bad table design. I have the suspicion that both the two digits in position 3 and 4 and the two digits in position 1 and 2 have a specific meaning in your business. If that is the case, you should probably store these as seperate columns. You can always paste the different values together for outputting as one column. Anyway, based on your ORDER BY clause, the BETWEEN predicate would read WHERE SUBSTRING(, 7, 2) + SUBSTRING (, 5, 2) BETWEEN SUBSTRING('M000011501', 7, 2) + SUBSTRING ('M000011501', 5, 2) AND SUBSTRING('M000011601', 7, 2) + SUBSTRING ('M000011601', 5, 2) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Jul 23 '05 #2

 P: n/a Hi It may be easier if you stored the correctly ordered string rather than the one used for display, and have the display string in a view/computed column or part of the select statement. You don't say how the rest of the string is arranged, but something like: SELECT SUBSTRING(, 7, 2) + SUBSTRING (, 5, 2) + SUBSTRING (, 1, 4) + SUBSTRING (, 9, 2), * FROM WHERE SUBSTRING(, 7, 2) + SUBSTRING (, 5, 2) + SUBSTRING (, 1, 4) + SUBSTRING (, 9, 2) between SUBSTRING('M000011501', 7, 2) + SUBSTRING ('M000011501', 5, 2) + SUBSTRING ('M000011501', 1, 4) + SUBSTRING ('M000011501', 9, 2) and SUBSTRING('M000011601', 7, 2) + SUBSTRING ('M000011601', 5, 2) + SUBSTRING ('M000011601', 1, 4) + SUBSTRING ('M000011601', 9, 2) ORDER BY SUBSTRING(, 7, 2), SUBSTRING (, 5, 2) will give your result. John wrote in message news:11**********************@f14g2000cwb.googlegr oups.com... We have a 10 digit primary key value in this format: M000123456. The order for this key is first determined by positions 3 and 4 in this example, then positions 1 and 2. So a brief sample of correct ordering would look like this: M000001501 M000011501 M000021501 M000001601 M000011601 M000021601 Now my question: how can I use a BETWEEN (or > and <) in my WHERE clause to get a range of values for this column? I use the following ORDER BY clause to control how the results are sorted, but I can't get the same logic to work with BETWEEN in a WHERE clause. ORDER BY SUBSTRING(, 7, 2), SUBSTRING (, 5, 2) How do I return values between M000011501 and M000011601 for example? Jul 23 '05 #3

 P: n/a Thanks for the reply Hugo. (I don't usually multi-post, btw, but...sorry.) Your solution works great, even though I didn't accurately post my sample data (where M000251501 is followed by M000001601). And oh, if only I could redesign the table! Out of my control however with this application . Thanks again, -- Ian Jul 23 '05 #4

### This discussion thread is closed

Replies have been disabled for this discussion. 