Блог

Получаю сообщение об ошибке преобразования varchar в float.

Есть таблица (не моего изготовления) со столбцом result , содержащим данные varchar. Я хотел бы преобразовать в float, чтобы захватить все значения> 180.0.

 SELECT result FROM table WHERE result > 180.0
  

Выдает ошибку. Интересно, однако:

 WITH temp AS (
  CASE
    WHEN ISNUMERIC(result)=1 THEN CAST(result as FLOAT)
    ELSE CAST(-1.0 AS FLOAT)
  END AS result
)
  

Это выполняется нормально. Когда я соединяю его с:

 SELECT temp.result FROM temp WHERE temp.result > 180.0
  

Я снова получаю ошибку. Мысли?

ОБНОВЛЕНИЕ: Запрошен полный код…

 WITH temp AS (
  SELECT
  CASE
    WHEN ISNUMERIC(result)=1 THEN CAST(result as FLOAT)
    ELSE CAST(-1.0 AS FLOAT)
  END AS result
  FROM table)

SELECT temp.result FROM temp WHERE temp.result > 180.0
  

Использование SQL-SERVER 2008 RC2.

Это означает, что у вас есть по крайней мере одна строка в таблице, к которой нельзя привести float . Выполнение CASE безопасно, но объединение CTE и добавление предложения WHERE впадает в распространенную ошибку программистов при написании T-SQL: этот порядок объявления подразумевает порядок выполнения. Программисты привыкли к императивному процедурному стилю C-подобных языков и не в состоянии понять декларативную природу SQL, основанную на множествах. Я уже писал ранее об этой проблеме и приводил примеры, когда ошибка вызывает ошибки:

• Функции T-SQL не подразумевают определенного порядка выполнения
• Короткое замыкание логического оператора SQL Server

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

после обновления

Хорошо, итак, я должен сообщить администратору, что в вашем случае код правильный в порядке выполнения, result столбец не может быть спроецирован без предварительной оценки CASE . Если бы ДЕЛО было в предложении WHERE, все было бы по-другому.

Ваша проблема в другом: ISNUMERIC . Эта функция имеет очень хорошее представление о том, что NUMERIC означает, и ранее привлекала внимание многих разработчиков. А именно, он принимает значения, которые ПРИВЕДЕНИЕ и ПРЕОБРАЗОВАНИЕ отклонит. Подобные, содержащие запятую:

 declare @n varchar(8000) = '1,000';
select isnumeric(@n);
select cast(@n as float);
select case when isnumeric(@n)=1 then cast(@n as float) else null end;
  

Итак, у вас есть значения, которые проходят ISNUMERIC тест, но не преобразуются. Просто предупреждаю, чем больше вы будете углубляться в этот подход, тем больше закрытых дверей вы обнаружите. Просто нет безопасного способа выполнить приведение, которое вам нужно на стороне сервера. В идеале исправьте модель данных (сделайте поле плавающим, если в нем хранятся значения с плавающей запятой). Кроме того, выполните сортировку данных и удалите все значения, которые не являются правильными значениями с плавающей запятой, и исправьте интерфейс / приложение, чтобы они больше не вводили новые, затем добавьте ограничение, которое вызовет ошибку, если появятся новые неправильные значения. Вы не сможете решить это в запросе, этот путь завален телами.

Со следующей версией SQL Server у вас появится новая функция TRY_CONVERT , которая решит вашу проблему.

Проверьте это:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

В принципе, есть некоторые известные проблемы / странности с функцией ISNUMERIC. Автор этой статьи предлагает создать новую функцию и использовать ее вместо ISNUMERIC. Я протестировал его с 1,0 значением, которое вы предложили, и, похоже, оно работает.

 
CREATE FUNCTION dbo.isReallyNumeric  
(  
    @num VARCHAR(64)  
)  
RETURNS BIT  
BEGIN  
    IF LEFT(@num, 1) = '-'  
        SET @num = SUBSTRING(@num, 2, LEN(@num))  

    DECLARE @pos TINYINT  

    SET @pos = 1   LEN(@num) - CHARINDEX('.', REVERSE(@num))  

    RETURN CASE  
    WHEN PATINDEX('%[^0-9.-]%', @num) = 0  
        AND @num NOT IN ('.', '-', ' ', '^') 
        AND LEN(@num)>0  
        AND @num NOT LIKE '%-%' 
        AND  
        (  
            ((@pos = LEN(@num) 1)  
            OR @pos = CHARINDEX('.', @num))  
        )  
    THEN  
        1  
    ELSE  
    0  
    END  
END  
GO 
  

Просто явное преобразование, но с небольшой изобретательностью ЗАМЕНА

 SELECT result FROM table WHERE CONVERT(float,REPLACE(result,',','.')) > 180.0