Databases
StackOverflow2013
Postsdbo
POProblems storing numeric data in text columns - SELECT...BETWEEN
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POProblems storing numeric data in text columns - SELECT...BETWEEN
    text
    copied!<p>A few years ago I worked on a system where a numeric primary key was stored in a [SQL Server] varchar column, so I quickly came unstuck when querying with a BETWEEN operator:</p> <pre><code>SELECT ID FROM MyTable WHERE ID BETWEEN 100 AND 110; </code></pre> <p>Results:</p> <pre><code>100 102 103 109 110 11 </code></pre> <p>This was simply bad design. However, I'm working on an 3rd-party ERP system, which as you can imagine needs to be generic and flexible; thus we have various tables where alphanumeric fields are provided where the business only uses numerics - so similar problems can occur.</p> <p>I'm guessing that this is a common enough issue; I have a simple enough solution, but I'm curious as to how others approach such problems.</p> <p>My simple solution is:</p> <pre><code>SELECT ID FROM MyTable WHERE ID BETWEEN iStartValue AND iEndValue AND (LENGTH(ID) = LENGTH(iStartValue) OR LENGTH(ID) = LENGTH(iEndValue)); </code></pre> <p>As you can possibly tell, this is an Oracle system, but I'm usually working in SQL Server - so perhaps database-agnostic solutions are preferable.</p> <p>Edit 1: Scratch that - I don't see why proprietary solutions aren't welcomed as well.</p> <p>Edit 2: Thanks for all the responses. I'm not sure whether I'm disappointed there is not an obvious, sophisticated solution, but I'm correspondingly glad that it doesn't appear that I've missed anything obvious!</p> <p>I think I still prefer my own solution; it's simple and it works - is there any reason why I shouldn't use it? I can't believe it is much, if any, less efficient that the other solutions offered.</p> <p>I realise that in an ideal world, this problem wouldn't exist; but unfortunately, I don't work in an ideal world, and often it's a case of making the best of a bad situation.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload