|
|
|
SSC 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!
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 3,231,
Visits: 64,321
|
|
|
|
|
|
SSCrazy
      
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
|
|
|
|
|
SSC 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?
|
|
|
|
|
Valued 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 ;
|
|
|
|
|
SSCarpal 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.
|
|
|
|
|
SSCrazy
      
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
|
|
|
|
|
SSCertifiable
       
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
|
|
|
|
|
Valued 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.
|
|
|
|
|
SSCarpal 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.
|
|
|
|