Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Adding comma separator to INT datatype Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2013 10:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 25, 2013 12:51 PM
Points: 29, Visits: 138
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!
Post #1413711
Posted Wednesday, January 30, 2013 10:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 3,231, Visits: 64,321
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
For tips on how to post your problems
Post #1413721
Posted Wednesday, January 30, 2013 11:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:39 AM
Points: 2,556, Visits: 4,398
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
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','')




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1413723
Posted Wednesday, January 30, 2013 11:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 25, 2013 12:51 PM
Points: 29, Visits: 138
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?
Post #1413726
Posted Wednesday, January 30, 2013 12:11 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:31 AM
Points: 65, Visits: 443
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
;

Post #1413738
Posted Thursday, January 31, 2013 2:38 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 4,247, Visits: 9,500
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.


____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1413933
Posted Thursday, January 31, 2013 3:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:39 AM
Points: 2,556, Visits: 4,398
...
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:
http://en.wikipedia.org/wiki/Decimal_mark


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1413949
Posted Thursday, January 31, 2013 5:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 6,730, Visits: 11,783
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1414005
Posted Thursday, January 31, 2013 5:45 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:31 AM
Points: 65, Visits: 443
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.
Post #1414031
Posted Thursday, January 31, 2013 5:57 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 4,247, Visits: 9,500
It it just another general purpose tool.


Anyone else picturing a man with a big hammer?


____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1414045
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse