cms9651 (8/14/2012)
Format number thousands separator with pointHi there, I need your help.
I have this number in my database output result of query:
1013473
I need this output: 1.013.473
Can you help me?
Thanks in advance.
You should probably be doing this in the presentation layer, rather than the database. But, here's one way you could do it: -
SELECT PARSENAME(CONVERT(VARCHAR(19), CONVERT(MONEY,randomBigInt), 1), 2)
FROM #testEnvironment;
How about performance?
BEGIN TRAN
SET NOCOUNT ON;
--== SOME SAMPLE DATA ==--
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
ABS(CHECKSUM(NEWID())) AS randomBigInt
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
--Holder variable to take display time out of the equation
DECLARE @HOLDER VARCHAR(19);
PRINT REPLICATE('=',80);
PRINT 'PARSENAME / CONVERT MONEY';
PRINT REPLICATE('=',80);
SET STATISTICS IO, TIME ON;
SELECT @HOLDER = PARSENAME(CONVERT(VARCHAR(19), CONVERT(MONEY,randomBigInt), 1), 2)
FROM #testEnvironment;
SET STATISTICS IO, TIME OFF;
ROLLBACK
================================================================================
PARSENAME / CONVERT MONEY
================================================================================
Table '#testEnvironment'. Scan count 1, logical reads 2102, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 609 ms, elapsed time = 610 ms.
Sorry, I've just noticed that you asked for dot separators not commas.
Instead, use this: -
SELECT REPLACE(PARSENAME(CONVERT(VARCHAR(19), CONVERT(MONEY,randomBigInt), 1), 2) COLLATE Latin1_General_BIN2, ',', '.')
FROM #testEnvironment;
Performance goes down over the commas because of the additional replace: -
BEGIN TRAN
SET NOCOUNT ON;
--== SOME SAMPLE DATA ==--
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
ABS(CHECKSUM(NEWID())) AS randomBigInt
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
--Holder variable to take display time out of the equation
DECLARE @HOLDER VARCHAR(19);
PRINT REPLICATE('=',80);
PRINT 'REPLACE / PARSENAME / CONVERT MONEY';
PRINT REPLICATE('=',80);
SET STATISTICS IO, TIME ON;
SELECT @HOLDER = REPLACE(PARSENAME(CONVERT(VARCHAR(19), CONVERT(MONEY,randomBigInt), 1), 2) COLLATE Latin1_General_BIN2, ',', '.')
FROM #testEnvironment;
SET STATISTICS IO, TIME OFF;
ROLLBACK
================================================================================
REPLACE / PARSENAME / CONVERT MONEY
================================================================================
Table '#testEnvironment'. Scan count 1, logical reads 2102, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 985 ms, elapsed time = 987 ms.