Итак, я просто потратил 5 часов на устранение проблемы, которая, как оказалось, вызвана не только старым ненадежным ISNUMERIC, но выглядит как моя проблема появляется только тогда, когда UDF, в котором ISNUMERIC объявлен WITH SCHEMABINDING, и вызывается внутри хранимого proc (у меня есть много работы, чтобы отделить его до тестового примера, но моя первая потребность чтобы заменить его чем-то надежным).
Любые рекомендации относительно хороших, эффективных замен для ISNUMERIC(). Очевидно, что действительно нужны вариации для int, money и т.д., Но что люди используют (желательно в T-SQL, потому что в этом проекте я ограничен SQL Server, потому что это большой объем Задача обработки SQL Server для SQL Server)?
Вы можете использовать функции T-SQL TRY_CAST() или TRY_CONVERT(), если вы используете SQL Server 2012 в качестве Bacon Bits в комментариях:
SELECT CASE WHEN TRY_CAST('foo' AS INT) IS NULL THEN 0 ELSE 1 END
SELECT CASE WHEN TRY_CAST(1 AS INT) IS NULL THEN 0 ELSE 1 END
Если вы используете SQL 2008 R2 или старше, вам придется использовать функцию .NET CLR и обернуть System.Decimal.TryParse().
В зависимости от обстоятельств и характеристик производительности валидации я иногда использую вариацию выражения LIKE. Например:
NOT LIKE '%[^0-9]%'
Обратите внимание, что этот конкретный пример довольно наивен. Это не гарантирует, что значение действительно для преобразования в конкретный тип данных. Он также не допускает знаков +/- или десятичных знаков, если вам это нужно.
Другим вариантом может быть запись расширенной хранимой процедуры на языке, таком как C, внесение ее в DLL и ее доступность для SQL Server.
Я не думаю, что для этого потребовалось бы слишком много строк кода, и, вероятно, это было бы быстрее, чем писать управляемую хранимую процедуру в .NET, потому что вы не понесете лишнюю подслушивание от загрузки CLR.
Здесь есть лакомый кусок информации: http://msdn.microsoft.com/en-us/library/ms175200.aspx
Вот код С++, который может сработать для вас:
using namespace std;
int checkNumber() {
int number = 0;
cin >> number;
cin.ignore(numeric_limits<int>::max(), '\n');
if (!cin || cin.gcount() != 1)
cout << "Not a number.";
else
cout << "Your entered: " << number;
return 0;
}
Следуя маршруту .NET CLR, вы можете пойти с регулярным выражением, особенно если вы ожидаете определенный диапазон чисел.
Как правило, я стараюсь не передавать в базу данных нетипизированные данные, поскольку лучше подходит для обработки на уровне приложения или для пакетного импорта обрабатывать его в службах интеграции SQL, чтобы данные поступали в правильно напечатано с самого начала.
Мне приходилось делать это много раз в прошлом, и, как правило, самым быстрым способом является создание собственной пользовательской функции для проверки того, что данные находятся в ожидаемом формате, поскольку большую часть времени накладные расходы вызывают на расширенный хранимый proc или управляемый код для простой проверки медленнее, чем просто в T-SQL.
В соответствии с поддержкой Microsoft единственный эффективный способ заменить функцию UDF - написать собственную версию функции .NET.
Конечно, если ваш администратор базы данных допускает следующее:).
Моя не делает: (.
Для SQL Server 2005 и выше.... используйте try/catch...
declare @test varchar(10), @num decimal
select @test = '0123A'
begin try
select @num = cast(@test as decimal)
print '1'
end try
begin catch
print '0'
end catch
печатает 0.
Измените @test = '01234' или @test = '01234.5', и он печатает 1.
Вы когда-нибудь будете обрабатывать числовые системы вне своего (человеческого) языка, например, китайского и т.д.? Если это так, я бы предложил использовать библиотеку libuninum.
IsNumeric(), похоже, имеет проблемы с пробелами: "D", "E", знаки доллара и всевозможные другие символы. То, что мы обычно хотим, это то, что говорит нам, будет ли CAST или CONVERT успешным. Этот UDF, хотя и не самый быстрый способ, очень хорошо работал у меня.
create function dbo.udf_IsNumeric(@str varchar(50))
returns int
as
begin
declare @rtn int
select @rtn =
case
when ltrim(rtrim(@str)) in('.', '-', '-.', '+', '+.') then 0
when ltrim(rtrim(@str)) like '%[^-+.0-9]%' then 0
else isnumeric(@str)
end
return @rtn
end
SQL 2012, вы можете использовать функцию TRY_PARSE() вместо ISNUMERIC().
SELECT
TRY_PARSE('123' as int) as '123'
,TRY_PARSE('abc' as int) as 'abc'
Как реализовать эти две функции:
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
CREATE FUNCTION dbo.isReallyInteger
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))
RETURN CASE
WHEN PATINDEX('%[^0-9-]%', @num) = 0
AND CHARINDEX('-', @num) <= 1
AND @num NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
THEN
1
ELSE
0
END
END
GO