Ошибка конверсии при преобразовании значения 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