如何将数字转换为数字、逗号分隔的格式化字符串?

Is there an easy way to convert a number (in my case an integer) to a comma separated nvarchar string?

是否有一种简单的方法可以将数字(在我的例子中是整数)转换为逗号分隔的nvarchar字符串?

For instance, if I had an int value of 1000000 stored in a field, how can I convert it to an nvarchar string with the outputted result of "1,000,000"?

例如，如果我在一个字段中存储了1000000的int值，我如何将它转换为一个nvarchar字符串，输出结果为“1,000,000”?

I could easily write a function to do this but I wanted to be sure there wasn't an easier way involving a call to either CAST or CONVERT.

我可以很容易地编写一个函数来实现这一点，但我希望确保没有更简单的方法涉及到调用CAST或CONVERT。

8 个解决方案

#1

For SQL Server 2012, or later, an easier solution is to use FORMAT ()^{Documentation}.
EG:

对于SQL Server 2012，或者更晚些，更容易的解决方案是使用FORMAT()文档。例如:

SELECT Format(1234567.8, '##,##0')

Results in: 1,234,568

结果:1234568

#2

The reason you aren't finding easy examples for how to do this in T-SQL is that it is generally considered bad practice to implement formatting logic in SQL code. RDBMS's simply are not designed for presentation. While it is possible to do some limited formatting, it is almost always better to let the application or user interface handle formatting of this type.

在T-SQL中找不到简单示例的原因是，在SQL代码中实现格式化逻辑通常被认为是糟糕的实践。RDBMS只是不是为表示而设计的。虽然可以做一些有限的格式，但是最好还是让应用程序或用户界面处理这种类型的格式化。

But if you must (and sometimes we must!) use T-SQL, cast your int to money and convert it to varchar, like this:

但是，如果您必须(有时我们必须)使用T-SQL，将int类型转换为money，并将其转换为varchar，如下所示:

select convert(varchar,cast(1234567 as money),1)

If you don't want the trailing decimals, do this:

如果你不想要后面的小数，可以这样做:

select replace(convert(varchar,cast(1234567 as money),1), '.00','')

Good luck!

好运！

#3

Not sure it works in tsql, but some platforms have to_char():

不确定它是否适用于tsql，但是有些平台有to_char():

test=#select to_char(131213211653.78, '9,999,999,999,999.99');
        to_char        
-----------------------
    131,213,211,653.78
test=# select to_char(131213211653.78, '9G999G999G999G999D99');
        to_char        
-----------------------
    131,213,211,653.78
test=# select to_char(485, 'RN');
     to_char     
-----------------
         CDLXXXV

As the example suggests, the format's length needs to match that of the number for best results, so you might want to wrap it in a function (e.g. number_format()) if needed.

如示例所示，格式的长度需要与数字的长度匹配以获得最佳结果，因此如果需要，您可能需要将其封装在一个函数中(例如number_format()))。

Converting to money works too, as point out by the other repliers.

正如其他报价者所指出的那样，兑换货币也是有效的。

test=# select substring(cast(cast(131213211653.78 as money) as varchar) from 2);
     substring      
--------------------
 131,213,211,653.78

#4

Quick & dirty for int to nnn,nnn...

快速和肮脏的从int到nnn,nnn…

declare @i int = 123456789
select replace(convert(varchar(128), cast(@i as money), 1), '.00', '')
>> 123,456,789

#5

You really shouldn't be doing that in SQL - you should be formatting it in the middleware instead. But I recognize that sometimes there is an edge case that requires one to do such a thing.

你真的不应该在SQL中这样做——你应该在中间件中格式化它。但我意识到，有时有一个边缘情况需要一个人去做这样的事情。

This looks like it might have your answer:

这似乎有你的答案:

How do I format a number with commas in T-SQL?

如何在T-SQL中使用逗号来格式化数字?

#6

I looked at several of the options. Here are my two favorites, because I needed to round the value.

我看了几个选项。这里是我的两个最爱，因为我需要四舍五入。

,DataSizeKB = replace(convert(varchar,Cast(Round(SUM(BigNbr / 0.128),0)as money),1), '.00','')
,DataSizeKB2   = format(Round(SUM(BigNbr / 0.128),0),'##,##0')
-----------------
--- below if the full script where I left DataSizeKB in both methods -----------
--- enjoy --------- 
--- Hank Freeman : hfreeman@msn.com 
-----------------------------------
--- Scritp to get rowcounts and Memory size of index and Primary Keys
   SELECT
   FileGroupName = DS.name
   ,FileGroupType =
      CASE DS.[type]
        WHEN 'FG' THEN 'Filegroup'
        WHEN 'FD' THEN 'Filestream'
        WHEN 'FX' THEN 'Memory-optimized'
        WHEN 'PS' THEN 'Partition Scheme'
        ELSE 'Unknown'
      END
   ,SchemaName = SCH.name
   ,TableName = OBJ.name
   ,IndexType =
      CASE IDX.[type]
        WHEN 0 THEN 'Heap'
        WHEN 1 THEN 'Clustered'
        WHEN 2 THEN 'Nonclustered'
        WHEN 3 THEN 'XML'
        WHEN 4 THEN 'Spatial'
        WHEN 5 THEN 'Clustered columnstore'
        WHEN 6 THEN 'Nonclustered columnstore'
        WHEN 7 THEN 'Nonclustered hash'
      END
   ,IndexName = IDX.name
   ,RowCounts = replace(convert(varchar,Cast(p.rows as money),1), '.00','')  --- MUST show for all types when no Primary key
   --,( Case WHEN IDX.[type] IN (2,6,7) then 0  else  p.rows  end )as Rowcounts_T
   ,AllocationDesc = AU.type_desc
/*
   ,RowCounts = p.rows  --- MUST show for all types when no Primary key
   ,TotalSizeKB2  = Cast(Round(SUM(AU.total_pages / 0.128),0)as int) -- 128 pages per megabyte
   ,UsedSizeKB    = Cast(Round(SUM(AU.used_pages / 0.128),0)as int) 
   ,DataSizeKB    = Cast(Round(SUM(AU.data_pages / 0.128),0)as int)
    --replace(convert(varchar,cast(1234567 as money),1), '.00','')
*/
   ,TotalSizeKB   = replace(convert(varchar,Cast(Round(SUM(AU.total_pages / 0.128),0)as money),1), '.00','') -- 128 pages per megabyte
   ,UsedSizeKB    = replace(convert(varchar,Cast(Round(SUM(AU.used_pages / 0.128),0)as money),1), '.00','') 
   ,DataSizeKB    = replace(convert(varchar,Cast(Round(SUM(AU.data_pages / 0.128),0)as money),1), '.00','')
   ,DataSizeKB2   = format(Round(SUM(AU.data_pages / 0.128),0),'##,##0')
   ,DataSizeKB3   = format(SUM(AU.data_pages / 0.128),'##,##0')
   --SELECT Format(1234567.8, '##,##0.00')
   ---
   ,is_default    = CONVERT(INT,DS.is_default)
   ,is_read_only = CONVERT(INT,DS.is_read_only)
  FROM
   sys.filegroups DS -- you could also use sys.data_spaces
    LEFT JOIN sys.allocation_units AU ON DS.data_space_id = AU.data_space_id
    LEFT JOIN sys.partitions PA
      ON (AU.[type] IN (1,3) AND
          AU.container_id = PA.hobt_id) OR
         (AU.[type] = 2 AND
          AU.container_id = PA.[partition_id])
    LEFT JOIN sys.objects OBJ ON PA.[object_id] = OBJ.[object_id]
    LEFT JOIN sys.schemas SCH ON OBJ.[schema_id] = SCH.[schema_id]
    LEFT JOIN sys.indexes IDX
      ON PA.[object_id] = IDX.[object_id] AND
         PA.index_id = IDX.index_id
    -----
    INNER JOIN 
      sys.partitions p ON obj.object_id = p.OBJECT_ID AND IDX.index_id = p.index_id
  WHERE
    OBJ.type_desc = 'USER_TABLE' -- only include user tables
    OR
    DS.[type] = 'FD' -- or the filestream filegroup
  GROUP BY
    DS.name ,SCH.name ,OBJ.name ,IDX.[type] ,IDX.name ,DS.[type]  ,DS.is_default   ,DS.is_read_only -- discard different allocation units
   ,p.rows  ,AU.type_desc  --- 
  ORDER BY
    DS.name ,SCH.name ,OBJ.name ,IDX.name
   ---
   ;

#7

From SQL Server 2012 (& above), FORMAT() method provides the quickest and easiest solution. Here are some tips.

从SQL Server 2012(及以上版本)中，FORMAT()方法提供了最快、最简单的解决方案。这里有一些建议。

Syntax: Format( value, format [, culture ] )

语法:格式(值、格式[、文化])

Returns: Format function returns NVarchar string formatted with the specified format and with optional culture. (Returns NULL for invalid format-string.)

返回:Format函数返回带指定格式和可选区域性的NVarchar字符串。(返回无效的格式化字符串的NULL。)

Note: The Format() function is consistent across CLR / all .NET languages and provides maximum flexibility to generate formatted output.

注意:Format()函数在CLR / all . net语言中是一致的，并且提供了生成格式化输出的最大灵活性。

Following are few format types that can be achieved using this function:

以下是使用此函数可以实现的几种格式类型:

Numeric/Currency formatting - 'C' for currency, 'N' number without currency symbol, 'x' for Hexa-decimals.

数字/货币格式-“C”表示货币，“N”数字没有货币符号，“x”表示“十六进制”。
Date/Time formatting - 'd' short date, 'D' long date, 'f' short full date/time, 'F' long full date/time, 't' short time, 'T' long time, 'm' month+day, 'y' year+month.

日期/时间格式——'d' short Date， 'd' long Date， 'f' short full Date/Time， 'f' long full Date/Time， 't' short Time， 't' long Time， 'm' month+day， 'y' year+month。
Custom formatting - you can form your own-custom format using certain symbols/characters, such as dd, mm, yyyy etc. (for Dates). hash (#) currency symbols (£ $ etc.), comma(,) and so on. See examples below.

自定义格式——您可以使用某些符号/字符(如dd、mm、yyyyy等)形成自己的自定义格式。散列(#)货币符号(£$等等),逗号(,)等等。请参见下面的例子。

Examples:

例子:

Examples of Built-in Numeric/Currency Formats:

内置数字/货币格式的例子:

   select FORMAT(1500350.75, 'c', 'en-gb') --> £1,500,350.75
   select FORMAT(1500350.75, 'c', 'en-au') --> $1,500,350.75
   select FORMAT(1500350.75, 'c', 'en-in') --> ₹ 15,00,350.75

Examples of Built-in Date Formats:

内置日期格式的例子:

   select FORMAT(getdate(), 'd', 'en-gb') --> 20/06/2017
   select FORMAT(getdate(), 'D', 'fr-fr') --> mardi 20 juin 2017
   select FORMAT(getdate(), 'F', 'en-us') --> Tuesday, June 20, 2017 10:41:39 PM
   select FORMAT(getdate(), 'T', 'en-gb') --> 22:42:29

Examples of Custom Formatting:

自定义格式的例子:

   select FORMAT(GETDATE(), 'dd/MM/yyyy') --> 20/06/2017
   select FORMAT(GETDATE(), 'dd-MMM-yyyy') --> 20/Jun/2017
   select FORMAT(GETDATE(), 'dd.MMMM.yyyy HH:mm:ss') --> 20.June.2017 22:47:20
   select FORMAT(123456789.75,'$#,#.00')  --> $123,456,789.75
   select FORMAT(123456789.75,'£#,#.0')   --> £123,456,789.8

See MSDN for more information.

有关更多信息，请参见MSDN。

#8

-5

remove the commas with a replace and convert:

将逗号替换为替换并转换:

CONVERT(INT,REPLACE([varName],',',''))

where varName is the name of the variable that has numeric values in it with commas

varName是变量的名称，其中包含数值和逗号

#1