Я хочу определить, является ли целое число (например, TryParse в .NET). Unfortunatelly ISNUMERIC не подходит мне, потому что я хочу анализировать только целые числа, а не каждый вид числа. Есть ли такая вещь, как ISINT или что-то еще?
Вот какой код, чтобы все было ясно. Если MY_FIELD не является int, этот код не сработает:
SELECT @MY_VAR = CAST(MY_FIELD AS INT)
FROM MY_TABLE
WHERE MY_OTHER_FIELD = 'MY_FILTER'
Спасибо
Здесь сообщение в блоге, описывающее создание IsInteger UDF.
В принципе, он рекомендует добавлять '.e0' к значению и использовать IsNumeric. Таким образом, все, что уже имеет десятичную точку, теперь имеет две десятичные точки, что делает IsNumeric ложным, а все, что уже выражено в научной нотации, недействительно с помощью e0.
В своей статье Могу ли я преобразовать эту строку в целое число?, Itzik Ben-Gan предоставляет решение в чистом T-SQL и другое, использующее CLR.
Какое решение вы должны выбрать?
Улучшено ли решение T-SQL или CLR? Преимущество использования T-SQL решение состоит в том, что вам не нужно выходить за пределы домена T-SQL программирование. Однако решение CLR имеет два важных преимущества: Это проще и быстрее. Когда я тестировал оба решения против таблицы который имел 1 000 000 строк, решение CLR заняло две секунды, а чем семь секунд (для решения T-SQL), чтобы работать на моем ноутбуке. Так в следующий раз вам нужно проверить, может ли данная строка быть преобразованный в целое число, вы можете включить решение T-SQL или CLR которые я представил в этой статье.
Если вы хотите только поддерживать T-SQL, тогда используйте чистое решение T-SQL. Если производительность важнее удобства, то используйте решение CLR.
Чистое решение T-SQL сложно. Он объединяет встроенную функцию ISNUMERIC с сопоставлением с образцом и литой, чтобы проверить, соответствует ли строка int.
SELECT keycol, string, ISNUMERIC(string) AS is_numeric,
CASE
WHEN ISNUMERIC(string) = 0 THEN 0
WHEN string LIKE '%[^-+ 0-9]%' THEN 0
WHEN CAST(string AS NUMERIC(38, 0))
NOT BETWEEN -2147483648. AND 2147483647. THEN 0
ELSE 1
END AS is_int
FROM dbo.T1;
Часть T-SQL для решения CLR проще. Вы вызываете функцию fn_IsInt так же, как вы бы назвали ISNUMERIC.
SELECT keycol, string, ISNUMERIC(string) AS is_numeric,
dbo.fn_IsInt(string) AS is_int
FROM dbo.T1;
Часть С# является просто оболочкой для функции синтаксического анализа .NET Int32.TryParse. Это работает, потому что SQL Server int и .NET Int32 являются 32-разрядными целыми знаками.
using System;
using System.Data.SqlTypes;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean fn_IsInt(SqlString s)
{
if (s.IsNull)
return SqlBoolean.False;
else
{
Int32 i = 0;
return Int32.TryParse(s.Value, out i);
}
}
};
Прочтите статью Itzik для полного объяснения этих образцов кода.
С sqlserver 2005 и более поздними версиями вы можете использовать классы символов, похожие на regex, с помощью оператора LIKE. См. Здесь.
Чтобы проверить, является ли строка неотрицательным целым числом (это последовательность десятичных цифр), вы можете проверить, что она не содержит других символов.
SELECT numstr
FROM table
WHERE numstr NOT LIKE '%[^0-9]%'
Примечание1: Это также возвращает пустые строки.
Примечание2: Использование LIKE '%[0-9]%' возвращает любую строку, содержащую хотя бы цифру.
Смотрите скрипку
WHERE IsNumeric(MY_FIELD) = 1 AND CAST(MY_FIELD as VARCHAR(5)) NOT LIKE '%.%'
Это, вероятно, самое простое решение. Если ваш MY_FIELD содержит .00 или что-то в этом роде. В этом случае отбросьте его на поплавок, чтобы удалить любые завершающие .00s
Посмотрите, поможет ли ниже запрос
SELECT *
FROM MY_TABLE
WHERE CHARINDEX('.',MY_FIELD) = 0 AND CHARINDEX(',',MY_FIELD) = 0
AND ISNUMERIC(MY_FIELD) = 1 AND CONVERT(FLOAT,MY_FIELD) / 2147483647 <= 1
Правило для предложения WHERE правильное: чтобы сделать функцию обернуть ее в CASE WHEN.
ISNUMERIC(table.field) > 0 AND PATINDEX('%[^0123456789]%', table.field) = 0
Эта работа с функцией IsNumeric будет работать:
выберите * из A, где ISNUMERIC (x) = 1 и X не нравится "%.%"
или Использовать
выберите * из A, где x не нравится '% [^ 0-9]%'
Я думаю, что что-то не так с вашим дизайном базы данных. Я думаю, что это действительно плохая идея, чтобы смешать varchar и числа в одном столбце? В чем причина этого?
Конечно, вы можете проверить, есть ли какие-либо символы, кроме [0-9], но представьте, что у вас есть 1 м строк в таблице, и вы проверяете каждую строку. Я думаю, что это не сработает.
В любом случае, если вы действительно хотите это сделать, я предлагаю сделать это на стороне клиента.
declare @i numeric(28,5) = 12.0001
if (@i/cast(@i as int) > 1)
begin
select 'this is not int'
end
else
begin
select 'this is int'
end
У меня такое чувство, что это так работает сатана, но в качестве альтернативы:
Как насчет TRY-CATCH?
DECLARE @Converted as INT
DECLARE @IsNumeric BIT
BEGIN TRY
SET @Converted = cast(@ValueToCheck as int)
SET @IsNumeric=1
END TRY
BEGIN CATCH
SET @IsNumeric=0
END CATCH
select IIF(@IsNumeric=1,'Integer','Not integer') as IsInteger
Это работает, хотя и только в SQL Server 2008 и выше.
Иногда вы не можете проектировать базу данных, вам просто нужно работать с тем, что вам дано. В моем случае это база данных, расположенная на компьютере, на которой у меня только есть доступ на чтение, с которым было с 2008 года.
Мне нужно выбрать из столбца в плохо спроектированной базе данных, которая является varchar с номерами 1-100, но иногда с произвольной строкой. Я использовал следующее, чтобы обойти его (хотя я бы хотел, чтобы я разработал всю базу данных).
SELECT A from TABLE where isnumeric(A)=1
Я не Pro в SQL, но как насчет проверки, если он делится на 1? Для меня это делает работу.
SELECT *
FROM table
WHERE fieldname % 1 = 0
Используйте PATINDEX
DECLARE @input VARCHAR(10)='102030.40'
SELECT PATINDEX('%[^0-9]%',RTRIM(LTRIM(@input))) AS IsNumber
ссылка http://www.intellectsql.com/post-how-to-check-if-the-input-is-numeric/
Почему бы просто не сделать что-то вроде:
CASE
WHEN ROUND(MY_FIELD,0)=MY_FIELD THEN CAST(MY_FIELD AS INT)
ELSE MY_FIELD
END
as MY_FIELD2