SQL Server: преобразование varchar в INT

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

Я попробовал это, но это не сработало. и дай мне ошибку:

select CAST(userID AS int) from audit

Ошибка:

Ошибка преобразования при преобразовании значения varchar '1581............................................................................................................................ 'к типу данных int.

Я select len(userID) from audit и он возвращает 128 символов, которые не являются пробелами.

Я попытался определить символы ASCII для тех, кто тянется после идентификатора и значения ASCII = 0.

Я также попытался LTRIM, RTRIM и заменить char(0) на '', но не работает.

Единственный способ это работает, когда я говорю фиксированное количество символов, как это ниже, но UserID не всегда 4 символа.

select CAST(LEFT(userID, 4) AS int) from audit

Ответ 1

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

UPDATE dbo.[audit]
  SET UserID = REPLACE(UserID, CHAR(0), '')
  WHERE CHARINDEX(CHAR(0), UserID) > 0;

Но тогда вам также нужно будет исправить все, что помещает эти плохие данные в таблицу в первую очередь. Тем временем попробуйте:

SELECT CONVERT(INT, REPLACE(UserID, CHAR(0), ''))
  FROM dbo.[audit];

Но это не долгосрочное решение. Исправьте данные (и тип данных, пока вы на нем). Если вы не можете исправить тип данных немедленно, вы можете быстро найти виновника, добавив ограничение проверки:

ALTER TABLE dbo.[audit]
  ADD CONSTRAINT do_not_allow_stupid_data
  CHECK (CHARINDEX(CHAR(0), UserID) = 0);

ИЗМЕНИТЬ

Итак, это определенно 4-значное целое число, за которым следуют шесть экземпляров CHAR (0). И обходной путь, который я опубликовал, определенно работает для меня:

DECLARE @foo TABLE(UserID VARCHAR(32));
INSERT @foo SELECT 0x31353831000000000000;

-- this succeeds:
SELECT CONVERT(INT, REPLACE(UserID, CHAR(0), '')) FROM @foo;

-- this fails:
SELECT CONVERT(INT, UserID) FROM @foo;

Пожалуйста, подтвердите, что этот код сам по себе (ну, в любом случае, первый SELECT, в любом случае) работает для вас. Если это произойдет, ошибка, которую вы получаете, связана с другим нечисловым символом в другой строке (а если нет, то, возможно, у вас есть сборка, где определенная ошибка не была исправлена). Чтобы попытаться сузить его, вы можете принимать случайные значения из следующего запроса, а затем прокручивать символы:

SELECT UserID, CONVERT(VARBINARY(32), UserID)
  FROM dbo.[audit]
  WHERE UserID LIKE '%[^0-9]%';

Итак, возьмите случайную строку, а затем вставьте вывод в такой запрос:

DECLARE @x VARCHAR(32), @i INT;
SET @x = CONVERT(VARCHAR(32), 0x...); -- paste the value here
SET @i = 1;
WHILE @i <= LEN(@x)
BEGIN
  PRINT RTRIM(@i) + ' = ' + RTRIM(ASCII(SUBSTRING(@x, @i, 1)))
  SET @i = @i + 1;
END

Это может занять несколько проб и ошибок, прежде чем вы столкнетесь с какой-либо строкой, которая не работает по какой-либо другой причине, чем CHAR(0) - поскольку вы не можете отфильтровывать строки, содержащие CHAR(0), потому что они могут содержать CHAR(0) и CHAR(something else). Для всех, кого мы знаем, у вас есть значения в таблице, например:

SELECT '15' + CHAR(9) + '23' + CHAR(0);

... который также не может быть преобразован в целое число, заменили ли вы CHAR(0) или нет.

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

Ответ 2

Этот вопрос имеет 91 000 просмотров, поэтому, возможно, многие люди ищут более общее решение проблемы в заголовке "преобразование ошибок varchar в INT"

Если вы используете SQL Server 2012+, одним из способов обработки этих недопустимых данных является использование TRY_CAST

SELECT TRY_CAST (userID AS INT)
FROM   audit

В предыдущих версиях вы могли использовать

SELECT CASE
         WHEN ISNUMERIC(RTRIM(userID) + '.0e0') = 1
              AND LEN(userID) <= 11
           THEN CAST(userID AS INT)
       END
FROM   audit

Оба возвращают NULL, если значение не может быть изменено.

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

CAST(REPLACE(userID COLLATE Latin1_General_Bin, CHAR(0),'') AS INT)

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

Ответ 3

Я бы попробовал обрезать номер, чтобы узнать, что вы получаете:

select len(rtrim(ltrim(userid))) from audit

если это вернет правильное значение, просто выполните:

select convert(int, rtrim(ltrim(userid))) from audit

если это не возвращает правильное значение, тогда я сделал бы замену, чтобы удалить пустое пространство:

 select convert(int, replace(userid, char(0), '')) from audit

Ответ 4

Это больше для кого-то Поиск результата, чем оригинал post-er. Это сработало для меня...

declare @value varchar(max) = 'sad';
select sum(cast(iif(isnumeric(@value) = 1, @value, 0) as bigint));

returns 0

declare @value varchar(max) = '3';
select sum(cast(iif(isnumeric(@value) = 1, @value, 0) as bigint));

returns 3