Ошибка конверсии при преобразовании значения nvarchar 'test' в тип данных int

Контекст: SQL Server 2008

У меня есть таблица mytable, которая содержит два столбца NVARCHAR id, title.

Все данные в столбце id фактически являются числовыми, за исключением одной строки, которая содержит значение "test".

Я хочу получить все идентификаторы между 10 и 15, поэтому мне нужен SQL Server для преобразования значений столбца id в INTEGER.

Я использую ISNUMERIC(id) = 1 для устранения не числовых значений, но SQL Server довольно странно с этим запросом.

SELECT 
    in.* 
FROM 
    (SELECT 
         id, title 
     FROM 
         mytable 
     WHERE 
         ISNUMERIC(id) = 1) in
WHERE 
    in.id BETWEEN 10 AND 15

Этот запрос вызывает следующую ошибку:

Ошибка конверсии при преобразовании значения nvarchar 'test' в тип данных int.

Внутренний запрос исключает строку с значением "test" id, поэтому "in" не должен содержать ее. Почему SQL Server все еще пытается его преобразовать?

Я что-то упустил? Как я могу обойти это?

P.S. Я пробовал WHERE CAST(in.id AS INTEGER) BETWEEN 10 AND 15, но не работал.

Ответ 1

Я должен добавить еще один способ в стиле XML:

SELECT * 
FROM mytable
WHERE CAST(id as xml).value('. cast as xs:decimal?','int') BETWEEN 10 AND 15

Преобразуйте id в XML, преобразуйте значение в xs:decimal, а затем конвертируйте в integer. Если нет числового значения, оно будет преобразовано в NULL.

Здесь вы можете прочитать о правилах кастомизации типов XML (ссылка).

Ответ 2

Используйте функцию TRY_CONVERT, это очень удобно.

SELECT id, 
       title 
FROM   mytable 
where  TRY_CONVERT(int, id) is not NULL 
and    TRY_CONVERT(int, id) BETWEEN 10 and 15

TRY_CONVERT возвращает null, если преобразование завершается неудачно.

И для вас ошибка, я полагаю, что оптимизатор запросов беспорядок что-то здесь. Взгляните на план выполнения, возможно, он фильтрует значения от 10 до 15 на первом месте. Мое решение всегда будет работать.

Как сказал другой комментатор в вашем случае, функция BETWEEN выполняется до ISNUMERIC. Вот простой пример:

select * into #temp2
from (
select 'test' a
union
select cast(1 as varchar) a
union 
select cast(2 as varchar) a
union 
select cast(3 as varchar) a
)z


SELECT a FROM ( 
SELECT a FROM #temp2 WHERE ISNUMERIC(a) = 1
) b
WHERE b.a BETWEEN 10 AND 15

Этот простой запрос является альтернативой:

SELECT a 
FROM #temp2 
WHERE ISNUMERIC(a) = 1 
and a BETWEEN 10 AND 15

Ответ 3

Могло создать новое поле для поиска:

Select id, title
    from (Select id, title, case id when 'test' then null else cast(id as int) end as trueint from mytable) n
    where n.trueint between 10 and 15

ИЛИ

Select id, title
from mytable
where case isnumeric(id) when 1 then cast(id as int) else null end between 10 and 15

Ответ 4

Одна из возможностей заключается в том, чтобы заставить двигатель работать в два этапа:

DECLARE @tbl TABLE(id NVARCHAR(MAX),title NVARCHAR(MAX));
INSERT INTO @tbl 
SELECT id, title FROM mytable WHERE ISNUMERIC(id) = 1;

SELECT t.*
FROM @tbl t
WHERE CAST(t.id AS INT) BETWEEN 10 AND 15