Проблемы с хранением числовых данных в текстовых столбцах - ВЫБРАТЬ… МЕЖДУ

Несколько лет назад я работал над системой, в которой числовой первичный ключ хранился в столбце [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: Спасибо за все ответы. Я не уверен, разочарован ли я, что нет очевидного, сложного решения, но я соответственно рад, что не кажется, что я что-то упустил очевидное!

Я думаю, что все еще предпочитаю свое собственное решение; это просто и работает - есть ли причина, почему я не должен использовать это? Я не могу поверить, что это намного, если таковые имеются, менее эффективно, чем другие решения, предлагаемые.

Я понимаю, что в идеальном мире этой проблемы не было бы; но, к сожалению, я не работаю в идеальном мире, и часто это случай, когда можно извлечь выгоду из плохой ситуации.

10.12.2008 14:53:16
Я не использовал Oracle в течение года или около того, но если вы получаете ошибки преобразования во время приведения, не получите ли вы их, когда они выполняют неявное преобразование в вашем утверждении выше?
Tom H 10.12.2008 17:40:49
LENGTH - это функция, предназначенная для работы со строками, поэтому нет.
RB. 12.12.2008 15:13:49
5 ОТВЕТОВ

Если вы уверены, что значения в 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

Проверка на «-» и «.» персонажи на самом деле не требуются. Я предполагаю, что ваши идентификаторы не могут быть отрицательными или десятичными.

7
10.12.2008 15:09:28
В первом примере были исторические данные, которые этому препятствовали - БД использовалась более чем одним приложением, и у нас не было возможности ее изменить. Во-вторых, многие из таблиц являются универсальными / многоцелевыми, поэтому в некоторых случаях альфы разрешались / использовались в других частях приложения.
CJM 10.12.2008 15:00:32
Приведение таким образом предотвратит использование индексов. Добро пожаловать в TableScan.
Amy B 10.12.2008 15:01:45
[В случае, если это неясно - CASTing при наличии буквенно-цифровых данных в таблицах вызвал ошибку (Ошибка SQL: ORA-01722: неверный номер)]
CJM 10.12.2008 15:01:50
Я обновил его, чтобы показать метод, который должен работать. Я не рекомендую это однако!
RB. 10.12.2008 15:10:29
Re: точка зрения Дэвида Б. - в этом случае вы можете использовать индекс на основе функций
Dave Costa 10.12.2008 16:10:50

Как насчет броска вместо этого.

SELECT ID FROM MyTable 
WHERE cast(ID as signed) BETWEEN cast(iStartValue as signed) AND cast(iEndValue as signed)

Данный синтаксис - MySQL, но для T-SQl существуют аналогичные операторы CAST.

1
10.12.2008 14:58:58
Спасибо Винсент. Да, я учел это, но это было неуместно (см. Комментарии к РБ)
CJM 10.12.2008 15:05:37

Я не знаю, может ли это сработать в вашей ситуации, но ...

Как насчет добавления фактического числового столбца в таблицу, заполненную значением (в SQL Server можно использовать вычисляемый столбец с установленным постоянным индексом)

В БД других поставщиков используется какой-то другой механизм для заполнения (триггер, материализованное представление и т. Д.)

а затем использовать этот столбец вместо одного varchar ...

2
10.12.2008 15:13:46
Это то, что я собирался предложить.
P Daddy 10.12.2008 15:16:38
Это хорошая идея, но в обоих случаях я не могу изменить схему. Во втором примере я добавляю функциональные возможности системы ERP, поэтому, к сожалению, я вынужден работать в рамках границ, установленных разработчиками ERP.
CJM 10.12.2008 16:13:18
Можете ли вы добавить вид? Вы можете поместить туда свой вычисляемый столбец (и кластерный индекс).
P Daddy 10.12.2008 22:03:58

Возможно, LPAD (id, 12, '') подойдет вам. Все значения столбцов должны быть шириной 12 с пробелами слева.

Также я был бы немного обеспокоен числовыми значениями в столбцах varchar2.

если вы делаете что-либо числовое, например аналитику, вы можете получить исключение для нечисловых данных.

1
10.12.2008 15:14:00

Другим вариантом было бы дополнить ваши цифры нулями слева и использовать для этого оператор before. По соображениям саргидности, вероятно, лучше включить это в качестве второго условия условия (чтобы все еще можно было использовать возможные индексы). Что-то вроде этого...

SELECT ID FROM MyTable 
WHERE  ID BETWEEN iStartValue AND iEndValue 
       And Right('0000000000' + ID, 10) Between iStartValue and iEndValue 

Я проверил это в SQL Server, и он возвращает правильные значения. Возможно, вам придется изменить это для работы с Oracle.

1
10.12.2008 15:20:54