在文本列中存储数字数据时出现问题 - SELECT ... BETWEEN

几年前,我在一个数字主键存储在[SQL Server] varchar列中的系统上工作,所以当使用BETWEEN运算符查询时,我很快就解开了:

SELECT ID FROM MyTable WHERE ID BETWEEN 100 AND 110;

结果:

100
102
103
109
110
11

这简直就是糟糕的设计.但是,我正在研究第三方ERP系统,你可以想象它需要通用和灵活; 因此,我们有各种表格,其中提供字母数字字段,其中业务仅使用数字 - 因此可能发生类似的问题.

我猜这是一个很常见的问题; 我有一个简单的解决方案,但我很好奇其他人如何解决这些问题.

我的简单解决方案是:

SELECT ID FROM MyTable 
WHERE ID BETWEEN iStartValue AND iEndValue 
AND (LENGTH(ID) = LENGTH(iStartValue)
 OR LENGTH(ID) = LENGTH(iEndValue));

您可能会说,这是一个Oracle系统,但我通常在SQL Server中工作 - 因此可能更喜欢与数据库无关的解决方案.

编辑1:从头开始 - 我不明白为什么专业解决方案也不受欢迎.

编辑2:感谢所有回复.我不确定我是否感到失望,没有一个明显的,复杂的解决方案,但我相应很高兴看起来我没有错过任何明显的错误!

我想我还是更喜欢自己的解决方案; 它很简单而且有效 - 我有什么理由不使用它吗？我无法相信其他解决方案提供的效率要低得多.

我意识到在理想世界中,这个问题不会存在; 但不幸的是,我不是在一个理想的世界里工作,而且往往是一个充分利用糟糕情况的案例.

如果您确定ID中的值只是数字,为什么不只是CAST它们

WHERE CAST(ID as int) BETWEEN iStartValue AND iEndValue

编辑1:应该使用的转换方法的扩展是使用子查询来提取所有数字记录.请注意 - 我不认为这种方法比上面建议的更好,我把它包括在内,因为它解决了问题!

SELECT ID 
FROM    (
    SELECT  ID
    FROM    MyTable 
    WHERE   ISNUMERIC(ID) = 1
    AND CHARINDEX ('.', ID) = 0
    AND CHARINDEX ('-', ID) = 0
    ) a
WHERE   CONVERT(bigint, ID)  BETWEEN 0 AND 12000
ORDER BY LENGTH(ID) ASC, ID

检查" - "和"." 字符并不是真正需要的.我假设你的ID不能是负数或小数.