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 ?
Réponses
Trop de publicités?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 -
.
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.
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 '.'.
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
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);