TSQL - заменить isnumeric = 0

У меня есть оператор выбора, и в этом операторе выбора у меня есть несколько столбцов, для которых я выполняю базовые вычисления (например, [Col1] * 3.14). Однако иногда я сталкиваюсь с нечисловыми значениями, и когда это происходит, вся хранимая процедура дает сбой из-за одной строки.

Я думал об использовании WHERE ISNUMERIC(Col1) <> 0, но тогда я бы исключил информацию в других столбцах.

Есть ли способ в TSQL как-то заменить все строки на NULL или 0??


tsql sql-server-2016 isnumeric
person SUMguy    schedule 16.03.2018    source источник
comment
Избавьтесь от некоторых возможных головных болей, используя TRY_CAST() вместо ISNUMERIC().   -  person Alex K.    schedule 16.03.2018
comment
ISNUMERIC отвечает на вопрос, на который никто не хочет знать ответ - можно ли преобразовать эту строку хотя бы в один из числовых типов данных, мне все равно, в какие именно?. Так здорово. Тот факт, что вам нужен decimal, а строка может быть преобразована только в тип money, действительно продвинул вас вперед...   -  person Damien_The_Unbeliever    schedule 16.03.2018

Ответы (4)


arrow_upward
0
arrow_downward

Я предпочитаю Try_Cast:

SELECT
    someValue
    ,TRY_CAST(someValue as int) * 3.14     AS TRY_CAST_to_int
    ,TRY_CAST(someValue as decimal) * 3.14 AS TRY_CAST_to_decimal
    ,IIF(ISNUMERIC(someValue) = 1, someValue, null) * 3.14 as IIF_IS_NUMERIC
FROM (values 
    ( 'asdf'), 
    ( '2' ),
    ( '1.55')
) s(someValue)
person wnutt    schedule 16.03.2018

arrow_upward
3
arrow_downward

Что-то типа...

SELECT blah1, blah2, blah3
   CASE WHEN ISNUMERIC(Col1) = 1 THEN [Col1] * 3.14 ELSE NULL END as whatever
FROM your_table

Можно также сделать так, чтобы..

  • Нечисловые значения должны быть преобразованы в числовые или NULL, если это то, что ожидается в столбце, и
  • Если ожидаются числа, то столбец должен быть в первую очередь числовым, а не символьным типом данных, что допускает ошибки такого типа.
person Jim Horn    schedule 16.03.2018
comment
insert into your_table (blah1,blah2,blah3,Col1) values (1,2,3,'$4.0') и попробуйте это, и вы все равно получите ошибку - один из примеров плохой работы ISNUMERIC. - person Damien_The_Unbeliever; 16.03.2018

arrow_upward
0
arrow_downward

ISNUMERIC — ужасный способ сделать это, так как слишком много вещей, идентифицируемых как NUMERIC, не могут быть умножены на тип данных, отличный от MONEY.

https://www.brentozar.com/archive/2018/02/fifteen-things-hate-isnumeric/

Это с треском проваливается, так как "-" является числовым...

DECLARE @example TABLE (numerics VARCHAR(10));

INSERT INTO @example VALUES ('-')

SELECT CASE WHEN ISNUMERIC(numerics) = 1 THEN numerics  * 3.14 ELSE NULL END  
FROM @example;

Вместо этого попробуйте TRY_CAST (хотя и измените точность DECIMAL в соответствии с вашими потребностями):

DECLARE @example TABLE (numerics VARCHAR(10));

INSERT INTO @example VALUES ('-')

SELECT TRY_CAST(numerics AS decimal(10,2)) * 3.14  FROM @example;
person Mark Sinkinson    schedule 16.03.2018
comment
слишком много вещей, которые идентифицируются как ЦИФРОВЫЕ, которые таковыми не являются. - не правда. Все, что заставляет ISNUMERIC возвращать 1, может быть преобразовано в по крайней мере один из числовых типов данных. Проблема в том, что нас не волнует такая туманная возможность — нас волнует, можно ли преобразовать ее в конкретный числовой тип данных, и это не тот вопрос, на который отвечает ISNUMERIC. Например. select CONVERT(money,'-') — совершенно правильное преобразование. Но вам все равно, потому что вы не планируете преобразовывать свой '-' в тип данных money. - person Damien_The_Unbeliever; 16.03.2018

arrow_upward
0
arrow_downward

trycast проверит определенный тип

declare @T table (num varchar(20));
insert into @T values ('12'), ('3.14'), ('5.6E12'), ('$120'), ('-'), (''), ('cc'), ('aa'), ('bb'), ('1/5'); 
select t.num, ISNUMERIC(t.num) as isnumeric
     , isnull(TRY_CONVERT(smallmoney, t.num), 0) as smallmoney 
     , TRY_CONVERT(float, t.num) as float
     , TRY_CONVERT(decimal(18,4), t.num) as decimal 
     , isnull(TRY_CONVERT(smallmoney, t.num), TRY_CONVERT(float, t.num)) as mix
from @T t

num                  isnumeric   smallmoney            float                  decimal
-------------------- ----------- --------------------- ---------------------- ---------------------------------------
12                   1           12.00                 12                     12.0000
3.14                 1           3.14                  3.14                   3.1400
5.6E12               1           0.00                  5600000000000          NULL
$120                 1           120.00                NULL                   NULL
-                    1           0.00                  NULL                   NULL
                     0           0.00                  0                      NULL
cc                   0           0.00                  NULL                   NULL
aa                   0           0.00                  NULL                   NULL
bb                   0           0.00                  NULL                   NULL
1/5                  0           0.00                  NULL                   NULL

интересно последнее все равно не получается

person paparazzo    schedule 16.03.2018