Выберите max int из столбца varchar

Я пытаюсь получить наибольшее число из столбца varchar, который включает в себя как числа, так и строки. Пример данных, с которыми я работаю:

BoxNumber
123
A5
789
B1

Мне нужно вернуть наибольшее число (789 в этом случае) из столбца, игнорируя нечисловые значения A5 и B1.

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

Ответы

Ответ 1

вам нужна комбинация из-за того, что isnumeric возвращает 1 для следующих вещей

select isnumeric('+'),isnumeric('5d2')

ваше предложение where будет таким образом

WHERE VALUE NOT LIKE '%[a-z]%'
        AND ISNUMERIC(VALUE) = 1

create table #bla (value varchar(50))
insert #bla values('123')
insert #bla values('a5')
insert #bla values('789')
insert #bla values('b1')

SELECT MAX(CAST(value AS Int)) FROM #bla
WHERE VALUE NOT LIKE '%[a-z]%'
    AND ISNUMERIC(VALUE) = 1

Я написал об этом здесь ISNUMERIC Trouble

Ответ 2

Вы можете попробовать

Select MAX(BoxNumber) from {table} where IsNumeric(BoxNumber) = 1

Ответ 3

Выбранный ответ работал у меня до тех пор, пока я не добавил это значение в таблицу temp вместе с остальными в примере:

insert #bla values('1234')

Я ожидал, что мой результат max() теперь будет 1234, но он остался на уровне 789. Возможно, это связано с некоторыми настройками сортировки, но я смог воспроизвести несколько баз данных. Я нашел, что этот запрос ниже работал у меня, но мне было бы интересно услышать, есть ли более эффективный способ сделать это. Кроме того, я не хотел включать десятичные значения, поэтому я исключил что-либо с периодом.

SELECT MAX(CAST(Value AS Int)) FROM #bla 
WHERE ISNUMERIC(Value)=1 
    AND Value NOT LIKE '%[a-z]%' 
    AND Value NOT LIKE '%.%'

Ответ 4

Почему бы не

SELECT MAX(CAST(Value AS Int)) FROM #bla 
WHERE ISNUMERIC(Value)=1 
    AND Value LIKE '%[0-9]%'

тогда вы имеете дело только с числовыми строками. В этом случае вам может не понадобиться ISNUMERIC()

Ответ 5

Вы должны проверить это решение на значения, такие как "+" и "-", поскольку я думаю, что функция IsNumeric может вернуть 1 для этих значений

Ответ 6

Посмотрите на листинг столбца на int, затем выберите MAX(). Я не знаю, что он будет делать для столбцов, содержащих буквы, но это стоит изучить.

http://doc.ddart.net/mssql/sql70/ca-co_1.htm

Ответ 7

Эти ответы только наполовину правы. Они преуспевают в изоляции числовых значений, но не понимают, что когда базовое поле является числом, функция Max оценивается как символы (независимо от каста), чтение слева направо, так что 789 > 1000, потому что 7 > 1. Способ вокруг это может состоять в том, чтобы забыть о кастинге и левом проводе числа с нулями до общей длины, когда должен работать режим Max в символе.