Voici le code d’une fonction de remplisage (pading) avec des zéros en tête pour des valeurs numériques.
Fonction de padding à zéro
CREATE FUNCTION dbo.F_PAD_LEADING_ZERO (@VALUE sql_variant,
@NOMBRE smallint)
RETURNS NVARCHAR(max)
BEGIN
RETURN
CASE
WHEN @VALUE < 0
THEN '-' + REPLICATE('0', @NOMBRE - LEN(CAST(@VALUE AS VARCHAR(64))-1)) + RIGHT(CAST(@VALUE AS VARCHAR(64)), LEN(CAST(@VALUE AS VARCHAR(64)))-1)
ELSE '+' + REPLICATE('0', @NOMBRE - 1 - LEN(CAST(@VALUE AS VARCHAR(64)))) + CAST(@VALUE AS VARCHAR(64))
END;
END
Exemple d’utilisation :
SELECT dbo.F_PAD_LEADING_ZERO(value_in_use, 32) AS num
FROM sys.configurations
num
--------------------------------------
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000001
+0000000000000000000000000000001
+0000000000000000000000000000001
+0000000000000000000000000000002
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000004096
+0000000000000000000000000000001
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000001036
+0000000000000000000000000002049
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000020
+0000000000000000000000000000600
-0000000000000000000000000000001
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000065536
+0000000000000000000000000000000
+0000000000000000000000000000005
+0000000000000000000000000000000
+0000000000000000000000000001024
-0000000000000000000000000000001
+0000000000000000000000000000016
+0000000000000000000000000008192
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000060
+0000000000000000000000000000000
+0000000000000000000000000000004
+0000000000000000000000000000000
+0000000000000000000000000000100
+0000000000000000000000000000000
+0000000000000000000000000000100
+0000000000000000000000000000001
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000002
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000001
+0000000000000000000000000000000
+0000000000000000000000000000001
+0000000000000000000000000000001
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
+0000000000000000000000000000000
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
