SQL Server: преобразование данных типа varchar в числовой

У меня есть таблица:

Account_Code | Desc
503100       | account xxx
503103       | account xxx
503104       | account xxx
503102A      | account xxx
503110B      | account xxx

Где Account_Code есть varchar.

Когда я создаю запрос ниже:

Select 
  cast(account_code as numeric(20,0)) as account_code,
  descr 
from account 
where isnumeric(account_code) = 1

Он хорошо работает, возвращая всю запись, которая имеет действительное числовое значение в столбце Account_Code.

Но когда я пытаюсь добавить другой select, вложенный в предыдущий sql:

select account_code,descr 
from 
(
  Select cast(account_code as numeric(20, 0)) as account_code,descr 
  from account 
  where isnumeric(account_code) = 1
) a 
WHERE account_code between 503100 and 503105

запрос вернет ошибку

Ошибка преобразования типа данных varchar в числовой.

Что там происходит?

Я уже преобразовал в числовое, если Account_Code допустимо, но кажется, что запрос все еще пытается обработать недействительную запись.

Мне нужно использовать предложение BETWEEN в моем запросе.

Ответы

Ответ 1

SQL Server 2012 и более поздние версии

Просто используйте Try_Convert вместо:

TRY_CONVERT принимает переданное ему значение и пытается преобразовать его в указанный тип_данных. Если листинг преуспевает, TRY_CONVERT возвращает значение как указанный тип_данных; если возникает ошибка, возвращается null. Однако, если вы запросите преобразование, которое явно не разрешено, TRY_CONVERT завершится с ошибкой.

Узнайте больше о Try_Convert.

SQL Server 2008 и ранее

Традиционным способом обработки этого является защита каждого выражения с помощью case-case, поэтому независимо от того, когда он будет оцениваться, он не создаст ошибку, даже если логически кажется, что оператор CASE не нужен. Что-то вроде этого:

SELECT
   Account_Code =
      Convert(
         bigint, -- only gives up to 18 digits, so use decimal(20, 0) if you must
         CASE
         WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
         ELSE X.Account_Code
         END
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      CASE
      WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
      ELSE X.Account_Code
      END
   ) BETWEEN 503100 AND 503205

Однако мне нравится использовать такие стратегии, как это с SQL Server 2005 и выше:

SELECT
   Account_Code = Convert(bigint, X.Account_Code),
   A.Descr
FROM
   dbo.Account A
   OUTER APPLY (
      SELECT A.Account_Code WHERE A.Account_Code NOT LIKE '%[^0-9]%'
   ) X
WHERE
   Convert(bigint, X.Account_Code) BETWEEN 503100 AND 503205

Что это значит, стратегически переключать значения Account_Code на NULL внутри таблицы X, если они не являются числовыми. Я изначально использовал CROSS APPLY, но как Mikael Eriksson, так удачно указав, это привело к той же ошибке, потому что парсер запросов столкнулся с той же проблемой оптимизации моей попытки чтобы заставить порядок выражения (предикат пускал его победил). Переключившись на OUTER APPLY, он изменил фактическое значение операции, чтобы X.Account_Code мог содержать NULL значения во внешнем запросе, что требует правильного порядка оценки.

Вам может быть интересно прочитать Erland Sommarskog запрос Microsoft Connect об этой проблеме с порядковым номером. Он на самом деле называет это ошибкой.

Здесь есть дополнительные проблемы, но я не могу их сейчас решить.

P.S. Сегодня у меня был мозговой штурм. Альтернативой "традиционному способу", который я предложил, является выражение SELECT с внешней ссылкой, которая также работает в SQL Server 2000. (Я заметил, что с момента обучения CROSS/OUTER APPLY я улучшил возможности своих запросов со старыми Также версии SQL Server - поскольку я становлюсь более универсальным с возможностями "внешней ссылки" в SELECT, ON и WHERE предложениях!)

SELECT
   Account_Code =
      Convert(
         bigint,
         (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
   ) BETWEEN 503100 AND 503205

Это намного короче, чем оператор CASE.

Ответ 2

Нет гарантии, что SQL Server не будет пытаться выполнить CONVERT до numeric(20,0), прежде чем запускать фильтр в предложении WHERE.

И даже если это произошло, ISNUMERIC не является адекватным, поскольку он распознает £ и 1d4 как числовые, ни один из которых не может быть преобразован в numeric(20,0). (*)

Разделите его на два отдельных запроса, первый из которых фильтрует результаты и помещает их в переменную temp или табличную переменную, вторая из которых выполняет преобразование. (Подзапросы и CTE неадекватны, чтобы оптимизатор не пытался выполнить преобразование перед фильтром)

Для вашего фильтра, скорее всего, используйте account_code not like '%[^0-9]%' вместо ISNUMERIC.

(*) ISNUMERIC отвечает на вопрос, который никто, как мне известно, никогда не хотел спросить: "Может ли эта строка быть преобразована в любой из числовых типов данных? Мне все равно,?" - когда, очевидно, большинство людей хотят спросить: "Может ли эта строка быть преобразована в x?" где x является конкретным целевым типом данных.

Ответ 3

Если вы используете SQL Server 2012, вы также можете использовать новую функцию TRY_PARSE():

Возвращает результат выражения, переведенный в запрошенные данные тип или null, если сбой в SQL Server 2012. Сбой только TRY_PARSE для преобразования из строки в дату/время и типы номеров.

Ответ 4

Я думаю, что проблема не в подзапросе, а в предложении WHERE внешнего запроса. Когда вы используете

WHERE account_code between 503100 and 503105

SQL-сервер попытается преобразовать каждое значение в поле Account_code в integer, чтобы проверить его в условии. Очевидно, что это не сработает, если в некоторых строках будут нецелые символы.

Ответ 5

спасибо, попробуйте это вместо

Select 
  STR(account_code) as account_code_Numeric,
  descr 
from account 
where  STR(account_code) = 1

Я рад помочь вам