Comment formater un nombre avec des virgules en T-SQL ?

Question

Je lance quelques requêtes administratives et compile les résultats de sp_spaceused dans SQL Server 2008 pour examiner les ratios d'espace données/index de certaines tables de ma base de données. Bien sûr, j'obtiens toutes sortes de grands nombres dans les résultats et mes yeux commencent à s'embuer. Ce serait vraiment pratique si je pouvais formater tous ces chiffres avec des virgules (987654321 devient 987,654,321). Il est amusant de constater qu'au cours des nombreuses années d'utilisation de SQL Server, ce problème ne s'est jamais posé, car la plupart du temps, le formatage se fait au niveau de la couche de présentation, mais dans ce cas, le résultat T-SQL dans SSMS est le suivant es la présentation.

J'ai envisagé de créer un simple UDF CLR pour résoudre ce problème, mais il me semble que cela devrait être faisable avec le bon vieux T-SQL. Je pose donc la question ici : comment faire du formatage numérique en T-SQL classique ?

Answer 1

Bien que je sois d'accord avec tous ceux, y compris l'OP, qui disent que le formatage devrait être fait dans la couche de présentation, ce formatage peut être accompli en T-SQL en coulant dans le fichier money et ensuite converti en varchar . Cela inclut toutefois les décimales de fin de ligne, qui peuvent être supprimées par une boucle avec la commande SUBSTRING .

SELECT CONVERT(varchar, CAST(987654321 AS money), 1)

Answer 2

Je recommanderais de remplacer Replace au lieu de Substring pour éviter les problèmes de longueur de chaîne :

REPLACE(CONVERT(varchar(20), (CAST(SUM(table.value) AS money)), 1), '.00', '')

Answer 3

Pour les implémentations de SQL Server 2012+, vous aurez la possibilité d'utiliser la fonction FORMAT pour appliquer le formatage des chaînes de caractères à des types de données autres que des chaînes de caractères.

Dans la question initiale, l'utilisateur avait demandé la possibilité d'utiliser des virgules comme séparateurs de milliers. Dans un fermé car question en double L'utilisateur avait demandé comment appliquer le formatage des devises. La requête suivante montre comment effectuer ces deux tâches. Elle démontre également l'application de la culture pour en faire une solution plus générique (en abordant la fonction de Tsiridis Dimitris pour appliquer le formatage spécial grec)

-- FORMAT
-- http://msdn.microsoft.com/en-us/library/hh213505(v=sql.110).aspx
-- FORMAT does not do conversion, that's the domain of cast/convert/parse etc
-- Only accepts numeric and date/time data types for formatting. 
--
-- Formatting Types
-- http://msdn.microsoft.com/en-us/library/26etazsy.aspx

-- Standard numeric format strings
-- http://msdn.microsoft.com/en-us/library/dwhawy9k.aspx
SELECT
    -- c => currency
    -- n => numeric
    FORMAT(987654321, N'N', C.culture) AS some_number
,   FORMAT(987654321, N'c', C.culture) AS some_currency
,   C.culture
FROM
    (
        -- Language culture names
        -- http://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx
        VALUES
            ('en-US')
        ,   ('en-GB')
        ,   ('ja-JP')
        ,   ('Ro-RO')
        ,   ('el-GR')
    ) C (culture);

SQLFiddle pour ce qui précède

Answer 4

J'ai essayé l'astuce de l'argent ci-dessus, et cela fonctionne très bien pour les valeurs numériques avec deux chiffres significatifs ou moins. J'ai créé ma propre fonction pour formater les nombres avec des décimales :

CREATE FUNCTION [dbo].[fn_FormatWithCommas] 
(
    -- Add the parameters for the function here
    @value varchar(50)
)
RETURNS varchar(50)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @WholeNumber varchar(50) = NULL, @Decimal varchar(10) = '', @CharIndex int = charindex('.', @value)

    IF (@CharIndex > 0)
        SELECT @WholeNumber = SUBSTRING(@value, 1, @CharIndex-1), @Decimal = SUBSTRING(@value, @CharIndex, LEN(@value))
    ELSE
        SET @WholeNumber = @value

    IF(LEN(@WholeNumber) > 3)
        SET @WholeNumber = dbo.fn_FormatWithCommas(SUBSTRING(@WholeNumber, 1, LEN(@WholeNumber)-3)) + ',' + RIGHT(@WholeNumber, 3)

    -- Return the result of the function
    RETURN @WholeNumber + @Decimal

END

Answer 5

Voici un autre UDF t-sql

CREATE FUNCTION dbo.Format(@num int)
returns varChar(30)
As
Begin
Declare @out varChar(30) = ''

  while @num > 0 Begin
      Set @out = str(@num % 1000, 3, 0) + Coalesce(','+@out, '')
      Set @num = @num / 1000
  End
  Return @out
End