Преобразование строки в int с возратом default в T-SQL

Быстрый ответ

Для конвертации строки в число используйте функцию TRY_CAST, обеспечивающую попытку преобразования и возвращающую NULL при ошибках. Для замены полученного NULL на ваше значение используйте ISNULL:

SELECT ISNULL(TRY_CAST(название_столбца AS INT), значение_по_умолчанию) FROM название_таблицы;

Не забудьте заменить название_столбца, значение_по_умолчанию и название_таблицы на соответствующие ваши значения. Этот подход надёжно и быстро заменит неконвертируемые строки на указанное значение.

Продвинутые методы преобразования

SQL Server 2012+: TRY_CONVERT

Для SQL Server 2012 и более новых версий используйте TRY_CONVERT для проверки соответствия типов данных. Если преобразование невозможно, в ответе вы получите NULL, который затем замените на значение по умолчанию.

SELECT ISNULL(TRY_CONVERT(INT, название_столбца), значение_по_умолчанию) FROM название_таблицы;

Подробнее об этом расскажет наш спикер на видео

SQL Server 2005-2008 R2: Пользовательская функция

Если ваш SQL Server не поддерживает TRY_CAST, можно создать пользовательскую функцию (UDF) для безопасного преобразования, обрабатывающую специфические символы и переполнения.

CREATE FUNCTION dbo.TryCastInt(@value NVARCHAR(MAX), @default INT)
RETURNS INT
AS
BEGIN
    IF ISNUMERIC(@value + 'e0') = 1
        RETURN CASE
            WHEN @value NOT LIKE '%[^0-9]%' AND CONVERT(BIGINT, @value) BETWEEN -2147483648 AND 2147483647
            THEN CAST(@value AS INT)
            ELSE @default
        END
    RETURN @default
END

Безопасное преобразование: TRY-CATCH

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

BEGIN TRY 
    SELECT CAST(название_столбца AS INT) FROM название_таблицы;
END TRY
BEGIN CATCH 
    SELECT значение_по_умолчанию;
END CATCH

Обработка дробей и нежелательных символов

Функция PARSENAME пригодится для преобразования строк с дробями или разделителями.

SELECT CAST(PARSENAME(REPLACE(название_столбца, '.', ''), 1) AS INT)

Чтобы стереть лишние символы, примените текстовые операции:

SELECT ISNULL(TRY_CAST(REPLACE(REPLACE(название_столбца, '$', ''), ',', '') AS INT), значение_по_умолчанию) FROM название_таблицы;

Визуализация

Вообразите коробки с числовыми ярлыками. Сканеры считывают эти ярлыки и переносят их в контейнеры с целочисленными значениями, в то время как некорректные ярлыки помечаются и попадают в контейнер по умолчанию:

Вот пример; сканирование с помощью ISNUMERIC и CAST:

SELECT 
    CASE WHEN ISNUMERIC(label + 'e0') = 1
        AND label NOT LIKE '%[^0-9]%' 
        AND CONVERT(BIGINT, label) BETWEEN -2147483648 AND 2147483647 THEN CAST(label AS INT)
    ELSE 0
    END 
FROM packages;

Разбор подводных камней ISNUMERIC

Для предотвращения ложных срабатываний ISNUMERIC настройте логику так, чтобы она игнорировала типичные исключения:

SELECT CASE 
    WHEN label LIKE '%[^0-9]%' THEN 0
    WHEN ISNUMERIC(label + 'e0') = 1 THEN CAST(label AS INT)
    ELSE 0
END

Предотвращение переполнений

Будьте бдительны и помните о вероятности переполнений. Убедитесь, что результаты конвертации соответствуют ожидаемому диапазону целых чисел:

SELECT CASE 
    WHEN TRY_CONVERT(BIGINT, label) BETWEEN -2147483648 AND 2147483647 THEN CAST(label AS INT)
    ELSE 0 
END

Тестируйте ваши функции

Чтобы убедиться в правильности преобразования, проведите ряд тестов:

DECLARE @testData TABLE (label NVARCHAR(MAX));
INSERT INTO @testData VALUES ('1234'), ('2147483648'), ('-2147483649'), ('123.45'), ('@#!');

SELECT label, dbo.TryCastInt(label, 0)
FROM @testData;

Полезные материалы

1. CAST and CONVERT (Transact-SQL) – SQL Server | Microsoft Learn — детальное руководство по использованию CAST и CONVERT в T-SQL.
2. TRY_CAST (Transact-SQL) – SQL Server | Microsoft Learn — использование TRY_CAST для безопасной конвертации.
3. ISNUMERIC (Transact-SQL) – SQL Server | Microsoft Learn — использование ISNUMERIC для проверки числовых значений.
4. SQL Server 2005 Try and Catch Exception Handling — описание обработки исключений при помощи TRY и CATCH в SQL Server.
5. String Functions (Transact-SQL) – SQL Server | Microsoft Learn — разнообразные функции для работы со строками, которые вы можете применять при преобразовании строки в число.