Adding comma separator to INT datatype
-
Hello to all gurus here!
Please excuse the dumbness of this question, but if I have an INT value of 999999 and want to display it formatted with the thousand separator of my choice; which could be either the US standard of a comma "999,999"or the European standard of a dot "999.999"; how do I go about it?
Any direction/help provided will be highly appreciated!
Thanks!
-
SQL doesn't exist to format data. Have the User Interface format the data.
I.e. set the number format in Report Builder/SSRS or in Excel or whatever the tool is.
If the data type is an INT, leave it as an INT within the database and the query because casting it as a string is not the way to make SQL do it right.
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
johnnycash (1/30/2013)
Hello to all gurus here!Please excuse the dumbness of this question, but if I have an INT value of 999999 and want to display it formatted with the thousand separator of my choice; which could be either the US standard of a comma "999,999"or the European standard of a dot "999.999"; how do I go about it?
Any direction/help provided will be highly appreciated!
Thanks!
I never seen European standard like that :w00t:
We use comma here as thousand separator and dot for decimals...
Usually such formatting is done not in database, but on UI (or report).
INT datatype cannot have commas or dots!
In SQL2012 there is a new FORMAT function which can do it easely, in pre-SQL2012 you will need to write custom code which will convert your INT to varchar. But as soon as you do this, your values will stop to be INTs (numbers) and will became strings with all relevant implications.
One of the ways you can do it:
SELECT REPLACE(CONVERT(VARCHAR,CAST(YourIntColumn AS MONEY),1), '.00','')
-
Thank you all and yes, I agree that all this should be done at the presentation layer and not at the DB level, but this is one of those things that I wondered if it could be done.
The thing is, YES, It can be done. Here's the code:
declare @days int, @text varchar(20)
set @days = 1890
select @days,substring(convert(varchar(20),cast(cast(@days as float) as money),1),1,charindex('.',convert(varchar(20),cast(cast(@days as float) as money),1))-1)
The only thing is the European format. which it does the decimal point instead of the comma to separate thousands.
But thank you everyone! I have learned something new today, which it is a good thing, right?
-
Here is one that handles all positive ints up to maxint of 10 places. I'd personally turn it into a function and handle negatives too.
declare @days int;
set @days = 1234567890;
select
case len(@days)
when 10 then stuff(stuff(stuff(cast(@days as varchar(10)), 2, 0, ','), 6, 0, ','), 10, 0, ',')
when 9 then stuff(stuff(cast(@days as varchar(10)), 4, 0, ','), 8, 0, ',')
when 8 then stuff(stuff(cast(@days as varchar(10)), 3, 0, ','), 7, 0, ',')
when 7 then stuff(stuff(cast(@days as varchar(10)), 2, 0, ','), 6, 0, ',')
when 6 then stuff(cast(@days as varchar(10)), 4, 0, ',')
when 5 then stuff(cast(@days as varchar(10)), 3, 0, ',')
when 4 then stuff(cast(@days as varchar(10)), 2, 0, ',')
else cast(@days as varchar(10))
end
;
-
johnnycash (1/30/2013)
Thank you all and yes, I agree that all this should be done at the presentation layer and not at the DB level, but this is one of those things that I wondered if it could be done.The thing is, YES, It can be done. Here's the code:
declare @days int, @text varchar(20)
set @days = 1890
select @days,substring(convert(varchar(20),cast(cast(@days as float) as money),1),1,charindex('.',convert(varchar(20),cast(cast(@days as float) as money),1))-1)
The only thing is the European format. which it does the decimal point instead of the comma to separate thousands.
But thank you everyone! I have learned something new today, which it is a good thing, right?
I'd be happier if the lesson you learned was that SQL Server is not a data presentation tool, rather it's a data storage and manipulation tool.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
...
The only thing is the European format. which it does the decimal point instead of the comma to separate thousands.
...
Who told you that? It's not European format. We don't use decimal points instead of comma to separate thousands:
-
Phil Parkin (1/31/2013)
johnnycash (1/30/2013)
Thank you all and yes, I agree that all this should be done at the presentation layer and not at the DB level, but this is one of those things that I wondered if it could be done.The thing is, YES, It can be done. Here's the code:
declare @days int, @text varchar(20)
set @days = 1890
select @days,substring(convert(varchar(20),cast(cast(@days as float) as money),1),1,charindex('.',convert(varchar(20),cast(cast(@days as float) as money),1))-1)
The only thing is the European format. which it does the decimal point instead of the comma to separate thousands.
But thank you everyone! I have learned something new today, which it is a good thing, right?
I'd be happier if the lesson you learned was that SQL Server is not a data presentation tool, rather it's a data storage and manipulation tool.
+1 I could not have said that better myself.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
If I purchase an application, I can use it any way I want to. I used to be an academic purist back when I first started in sql 22 years ago. I had one "right" way to do things. I was so self-limiting in my behavior. Now I dominate the server and make it give me what I want. Most sqlservers hover around 3% cpu usage; why waste time optimizing when you can be solving new problems...like formatting text output on a "sacred" sqlserver? Why? Because I can and it's mine to play with how I want. It it just another general purpose tool.
-
It it just another general purpose tool.
Anyone else picturing a man with a big hammer? 🙂
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Phil Parkin (1/31/2013)
It it just another general purpose tool.
Anyone else picturing a man with a big hammer? 🙂
-
Bill Talada (1/31/2013)
If I purchase an application, I can use it any way I want to. I used to be an academic purist back when I first started in sql 22 years ago. I had one "right" way to do things. I was so self-limiting in my behavior. Now I dominate the server and make it give me what I want. Most sqlservers hover around 3% cpu usage; why waste time optimizing when you can be solving new problems...like formatting text output on a "sacred" sqlserver? Why? Because I can and it's mine to play with how I want. It it just another general purpose tool.CREATE TABLE FormatNumbersInMyGeneralTool(Number VARCHAR(10) NOT NULL);
INSERT INTO FormatNumbersInMyGeneralTool VALUES ('9'), ('1,000,000');
SELECT
MAX(Number) AS BiggestNumber,
SUM(Number) AS Total,
AVG(Number) AS Average
FROM
FormatNumbersInMyGeneralTool;
-
Bill Talada (1/31/2013)
If I purchase an application, I can use it any way I want to. I used to be an academic purist back when I first started in sql 22 years ago. I had one "right" way to do things. I was so self-limiting in my behavior. Now I dominate the server and make it give me what I want. Most sqlservers hover around 3% cpu usage; why waste time optimizing when you can be solving new problems...like formatting text output on a "sacred" sqlserver? Why? Because I can and it's mine to play with how I want. It it just another general purpose tool.Not the SQL Servers I manage. I most certainly do not want to waste processing power on string manipulation if I can help it. I do not choose to do this kind of work in SQL Server so by help it I mean steering other people away from placing logic into the data tier that could just as easily, and likely more efficiently and more easily scaled out (as in application server), be done somewhere else in the stack.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Bill Talada (1/31/2013)
If I purchase an application, I can use it any way I want to. I used to be an academic purist back when I first started in sql 22 years ago. I had one "right" way to do things. I was so self-limiting in my behavior. Now I dominate the server and make it give me what I want. Most sqlservers hover around 3% cpu usage; why waste time optimizing when you can be solving new problems...like formatting text output on a "sacred" sqlserver? Why? Because I can and it's mine to play with how I want. It it just another general purpose tool.3% today, 30% next year ... quite a few folk visiting this forum make a good living off the back of this very rash statement, optimising code which was written without a nod to performance when the server was singing and dancing with dev load - and killing it now the code's in production and a year or two of business has accumulated. Make it work, make it fast, make it pretty.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Eugene,
From that link you placed:
For ease of reading, numbers with many digits before or after the decimal mark may be divided into groups using a delimiter, with the counting of groups starting from the decimal mark in both directions.[11] This delimiter is usually called a "thousands separator", because the digits are usually in groups of three, that is, thousands. The most general name for this delimiter is "digit group separator", because thousands are not always the relevant group. For example, in various countries (e.g., China, India, and Japan), there have been traditional conventions of grouping by 2 or 4 digits. These conventions are still observed in some contexts, although the 3-digit group convention is also well-known and often used. Making groups of three digits also emphasizes that there is a base-1000 of the numeral system that is being used, which is not always a given (for example, in computer science).
As with the decimal mark, there have been several common conventions for which character to use for the digit group separator. If the decimal mark is a point, the digit group separator is often a comma or a space. If the decimal mark is a comma, the digit group separator is often a point or a space.
Countries using Arabic numerals with decimal comma
Countries where a comma "," is used to mark the radix point include:
Albania, Andorra, Argentina, Armenia, Austria, Azerbaijan, Belarus, Belgium, Bolivia, Bosnia and Herzegovina, Brazil, Bulgaria, Cameroon, Canada (French-speaking), Chile, Colombia, Costa Rica, Croatia (comma used officially, but both forms are in use elsewhere), Cuba, Cyprus, Czech Republic, Denmark, Ecuador, Estonia, Faroes, Finland, France, Germany, Georgia, Greece, Greenland, Honduras, Hungary, Iceland, Indonesia, Italy, Kazakhstan, Kirgistan, Latvia, Lebanon, Lithuania, Luxembourg (uses both marks officially), Macau (in Portuguese text), Macedonia, Moldova, Mongolia, Morocco, Netherlands, Norway, Paraguay, Peru, Poland, Portugal, Romania, Russia, Serbia, Slovakia, Slovenia, South Africa (officially[15]), Spain, Sweden, Tunisia, Turkey, Ukraine, Uruguay, Uzbekistan, Venezuela, Vietnam.
I am just going from what my Europe users have complained all along: The invoices/reports coming out of the "american" system when the currency is Euros, are difficult to read because of the decimal notation.
But thanks anyways.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply