TSQL - Transformez une chaîne de caractères en un nombre entier ou renvoyez la valeur par défaut

Question

Existe-t-il un moyen en T-SQL de convertir un nvarchar en int et de renvoyer une valeur par défaut ou NULL si la conversion échoue ?

Answer 1

Oui :). Essaie ça :

DECLARE @text AS NVARCHAR(10)

SET @text = '100'
SELECT CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS INT) ELSE NULL END
-- returns 100

SET @text = 'XXX'
SELECT CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS INT) ELSE NULL END
-- returns NULL

ISNUMERIC() a quelques problèmes signalés par Fedor Hajdu .

Il renvoie la réponse vraie pour les chaînes de caractères comme $ (est la monnaie), , o . (les deux sont des séparateurs), + y - .

Answer 2

Si vous êtes sur SQL Server 2012 (ou plus récent) :

Utilisez le Fonction TRY_CONVERT .

Si vous êtes sur SQL Server 2005, 2008, ou 2008 R2 :

Créez une fonction définie par l'utilisateur. Cela permettra d'éviter les problèmes que Fedor Hajdu a mentionné en ce qui concerne la monnaie, les nombres fractionnaires, etc :

CREATE FUNCTION dbo.TryConvertInt(@Value varchar(18))
RETURNS int
AS
BEGIN
    SET @Value = REPLACE(@Value, ',', '')
    IF ISNUMERIC(@Value + 'e0') = 0 RETURN NULL
    IF ( CHARINDEX('.', @Value) > 0 AND CONVERT(bigint, PARSENAME(@Value, 1)) <> 0 ) RETURN NULL
    DECLARE @I bigint =
        CASE
        WHEN CHARINDEX('.', @Value) > 0 THEN CONVERT(bigint, PARSENAME(@Value, 2))
        ELSE CONVERT(bigint, @Value)
        END
    IF ABS(@I) > 2147483647 RETURN NULL
    RETURN @I
END
GO

-- Testing
DECLARE @Test TABLE(Value nvarchar(50))    -- Result
INSERT INTO @Test SELECT '1234'            -- 1234
INSERT INTO @Test SELECT '1,234'           -- 1234
INSERT INTO @Test SELECT '1234.0'          -- 1234
INSERT INTO @Test SELECT '-1234'           -- -1234
INSERT INTO @Test SELECT '$1234'           -- NULL
INSERT INTO @Test SELECT '1234e10'         -- NULL
INSERT INTO @Test SELECT '1234 5678'       -- NULL
INSERT INTO @Test SELECT '123-456'         -- NULL
INSERT INTO @Test SELECT '1234.5'          -- NULL
INSERT INTO @Test SELECT '123456789000000' -- NULL
INSERT INTO @Test SELECT 'N/A'             -- NULL
SELECT Value, dbo.TryConvertInt(Value) FROM @Test

Référence : J'ai utilisé cette page largement lors de la création de ma solution.

Answer 3

Je préférerais créer une fonction comme TryParse ou utiliser T-SQL. TRY-CATCH bloc pour obtenir ce que vous vouliez.

ISNUMERIC ne fonctionne pas toujours comme prévu. Le code donné précédemment échouera si vous le faites :

SET @text = "$

Le signe $ peut être converti en un type de données monétaire, comme suit ISNUMERIC() renvoie vrai dans ce cas. Il en sera de même pour les caractères '-' (moins), ',' (virgule) et '.'.

Answer 4

Comme cela a été mentionné, vous risquez de rencontrer plusieurs problèmes si vous utilisez ISNUMERIC :

-- Incorrectly gives 0:
SELECT CASE WHEN ISNUMERIC('-') = 1 THEN CAST('-' AS INT) END   

-- Error (conversion failure):
SELECT CASE WHEN ISNUMERIC('$') = 1 THEN CAST('$' AS INT) END
SELECT CASE WHEN ISNUMERIC('4.4') = 1 THEN CAST('4.4' AS INT) END
SELECT CASE WHEN ISNUMERIC('1,300') = 1 THEN CAST('1,300' AS INT) END

-- Error (overflow):
SELECT CASE WHEN ISNUMERIC('9999999999') = 1 THEN CAST('9999999999' AS INT) END

Si vous voulez une conversion fiable, vous devrez en coder une vous-même. Le snippet ci-dessous fonctionne sur les entiers non négatifs. Il vérifie que votre chaîne de caractères ne contient pas de caractères non numériques, qu'elle n'est pas vide et qu'elle ne déborde pas (en dépassant la valeur maximale de la balise int ). Cependant, il donne également NULL pour les entiers valides dont la longueur dépasse 10 caractères en raison des zéros de tête.

SELECT 
    CASE WHEN @text NOT LIKE '%[^0-9]%' THEN
         CASE WHEN LEN(@text) BETWEEN 1 AND 9 
                OR LEN(@text) = 10 AND @text <= '2147483647' 
              THEN CAST (@text AS INT)
         END
    END 

Si vous souhaitez prendre en charge n'importe quel nombre de zéros initiaux, utilisez le modèle ci-dessous. L'imbrication CASE Bien qu'elles soient lourdes, les déclarations sont nécessaires pour favoriser l'évaluation des courts-circuits et réduire la probabilité d'erreurs (découlant, par exemple, du passage d'une longueur négative à l'adresse LEFT ).

SELECT 
    CASE WHEN @text NOT LIKE '%[^0-9]%' THEN
         CASE WHEN LEN(@text) BETWEEN 1 AND 9 THEN CAST (@text AS INT)
              WHEN LEN(@text) >= 10 THEN
              CASE WHEN LEFT(@text, LEN(@text) - 10) NOT LIKE '%[^0]%'
                    AND RIGHT(@text, 10) <= '2147483647'
                   THEN CAST (@text AS INT)
              END
         END
    END

Si vous voulez soutenir des actions positives y des nombres entiers négatifs avec un nombre quelconque de zéros de tête :

SELECT 
         -- Positive integers (or 0):
    CASE WHEN @text NOT LIKE '%[^0-9]%' THEN
         CASE WHEN LEN(@text) BETWEEN 1 AND 9 THEN CAST (@text AS INT)
              WHEN LEN(@text) >= 10 THEN
              CASE WHEN LEFT(@text, LEN(@text) - 10) NOT LIKE '%[^0]%'
                    AND RIGHT(@text, 10) <= '2147483647'
                   THEN CAST (@text AS INT)
              END
         END
         -- Negative integers:
         WHEN LEFT(@text, 1) = '-' THEN
         CASE WHEN RIGHT(@text, LEN(@text) - 1) NOT LIKE '%[^0-9]%' THEN
              CASE WHEN LEN(@text) BETWEEN 2 AND 10 THEN CAST (@text AS INT)
                   WHEN LEN(@text) >= 11 THEN
                   CASE WHEN SUBSTRING(@text, 2, LEN(@text) - 11) NOT LIKE '%[^0]%'
                         AND RIGHT(@text, 10) <= '2147483648'
                        THEN CAST (@text AS INT)
                   END
              END
         END
    END

Une alternative plus concise (basée sur Commentaire d'adrianm ) :

SELECT
    CASE WHEN ISNUMERIC(@text + '.e0') = 1 THEN 
         CASE WHEN CONVERT(float, @text) BETWEEN -2147483648 AND 2147483647 
              THEN CONVERT(int, @text) 
         END 
     END

Answer 5

La réponse de Joseph indique qu'ISNUMERIC gère également la notation scientifique comme '1.3e+3' mais sa réponse ne gère pas ce format de nombre.

Le lancement vers une monnaie ou un flotteur gère d'abord les questions monétaires et scientifiques :

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TryConvertInt]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[TryConvertInt]
GO

CREATE FUNCTION dbo.TryConvertInt(@Value varchar(18))
RETURNS bigint
AS
BEGIN
    DECLARE @IntValue bigint;

    IF (ISNUMERIC(@Value) = 1)
        IF (@Value like '%e%')
            SET @IntValue = CAST(Cast(@Value as float) as bigint);
        ELSE
            SET @IntValue = CAST(CAST(@Value as money) as bigint);
    ELSE
        SET @IntValue = NULL;

    RETURN @IntValue;
END

La fonction échouera si le nombre est supérieur à un bigint.

Si vous voulez renvoyer une valeur par défaut différente, laissez cette fonction telle qu'elle est générique et remplacez le null par la suite :

SELECT IsNull(dbo.TryConvertInt('nan') , 1000);