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.
This question is related to
sql
sql-server
sql-server-2005
tsql
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
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
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:
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
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 : [email protected]
-----------------------------------
--- 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
---
;
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!
Source: Stackoverflow.com