How to use BETWEEN with custom-ordered values

[ian.proffer]

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(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)
How do I return values between M000011501 and M000011601 for example?

[Hugo Kornelis]

On 19 Dec 2004 16:36:14 -0800, ia*********@gmail.com wrote:

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(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 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(<columnname>, 7, 2) + SUBSTRING (<columnname>, 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)

[John Bell]

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(<fieldname>, 7, 2) + SUBSTRING (<fieldname>, 5, 2) +
SUBSTRING (<fieldname>, 1, 4) + SUBSTRING (<fieldname>, 9, 2),
* FROM <tablename>
WHERE SUBSTRING(<fieldname>, 7, 2) + SUBSTRING (<fieldname>, 5, 2) +
SUBSTRING (<fieldname>, 1, 4) + SUBSTRING (<fieldname>, 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(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)

will give your result.

John
<ia*********@gmail.com> 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(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)
How do I return values between M000011501 and M000011601 for example?

[ian.proffer]

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

Thanks again,
-- Ian