How do I convert a number to a numeric, comma-separated formatted string?

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

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"?

I could easily write a function to do this but I wanted to be sure there wasn't an easier way involving a ca开发者_运维百科ll to either CAST or CONVERT.

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.

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

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!

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

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

Results in: 1,234,568

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

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

Not sure it works in tsql, but some platforms have 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.

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

Although formatting belongs to the Presentation layer, SQL Server 2012 and above versions provide FORMAT() function which provides one of the quickest and easiest way to format output. Here are some tips & examples:

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.)

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

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.

• 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.

• 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.

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(), 'ddd  dd/MM/yyyy')  --> Tue 20/06/2017
   select FORMAT(GETDATE(), 'dddd dd-MMM-yyyy') --> Tuesday 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 on FORMAT() function.

For SQL Server 2008 or below convert the output to MONEY first then to VARCHAR (passing "1" for the 3rd argument of CONVERT() function to specify the "style" of output-format), e.g.:

   select CONVERT(VARCHAR, CONVERT(MONEY, 123456789.75), 1)    --> 123,456,789.75

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.

This looks like it might have your answer:

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

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
   ---
   ;

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