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

Question

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 call to either CAST or CONVERT

User · Answer

Quick  amp  dirty for int to nnn nnn     declare  i int   123456789 select replace convert varchar 128   cast  i as money   1     00        gt  gt  123 456 789

User · Answer

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

User · Answer

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  amp  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   -- gt    1 500 350 75     select FORMAT 1500350 75   c    en-au   -- gt   1 500 350 75     select FORMAT 1500350 75   c    en-in   -- gt    15 00 350 75   Examples of Built-in Date Formats       select FORMAT getdate     d    en-gb   -- gt  20 06 2017     select FORMAT getdate     D    fr-fr   -- gt  mardi 20 juin 2017     select FORMAT getdate     F    en-us   -- gt  Tuesday  June 20  2017 10 41 39 PM     select FORMAT getdate     T    en-gb   -- gt  22 42 29   Examples of Custom Formatting       select FORMAT GETDATE     ddd  dd MM yyyy    -- gt  Tue 20 06 2017     select FORMAT GETDATE     dddd dd-MMM-yyyy   -- gt  Tuesday 20-Jun-2017     select FORMAT GETDATE     dd MMMM yyyy HH mm ss   -- gt  20 June 2017 22 47 20     select FORMAT 123456789 75       00    -- gt   123 456 789 75     select FORMAT 123456789 75        0     -- gt    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     -- gt  123 456 789 75

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql