January 7, 2013 at 5:31 am
I have an sql query which returns some numeric values that sql server is representing with a decimal point. Since we are using a Spanish version of Excel, excel is expecting a decimal comma instead of a decimal point, thus when I copy data in SSMS then paste into Excel value 1.24219 is interpreted as 124.219 when it should be 1,24219
I have solved this using the replace function in my query, as in:
select replace(1.24129,'.',',')
the drawback is I have to use a replace function for every decimal column which makes all sql queries harder to read and is more time consuming, both for writing and for the sql execution itself.
A second workaround is going into excel, options, advanced options, and telling excel it won't use local regional settings but always use '.' as a decimal separator, which is not nice either because we are not used to see the point as a decimal separator in Spanish.
I think there must be a more elegant way to let SQL Server Management Studio I want to see all decimal separators with a comma instead of a point, is there any way of doing this?
Thank you,
January 7, 2013 at 12:25 pm
hi there,
I think you are one of thousands having this problem. I personally still don't know any easy solution to this. I personally work with a third solution: copy the result into a test editor, use the "find and replace"-command to replace the dot with a comma, and then insert it into the excel file.
Otherwise I use the method with the replalce-function too.
January 7, 2013 at 12:57 pm
I don't think that's an issue with SQL Server. I think that's an issue with Management Studio. Is the workstation you're running queries on set to the proper localization settings? I think SSMS inherits those from the workstation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 1:03 pm
This is a problem many people have. In the german language the decimal separator is a comma too. When having the english database system all decimal separators are returned with a dot.
When switching the local country settings the decimal separator is changed in management studio but also in excel. And, by the way, in ALL other programs what you don't want.
What is needed is a configuration for the decimal separator for the management studio only. So one could change the output to get the comma as decimal separator and copy it to excel without having problems. But this configuration is still not existing.
January 7, 2013 at 1:29 pm
Since SSMS is designed to inherit that kind of thing from Windows, I think the only solution would be to run SSMS in a virtual computer, with the localization set on that. Seems like overkill for this, but it should get the job done.
Of course, another solution would be to build an add-on for SSMS that would do this kind of thing. Should be possible. After all RedGate built add-ons for SSMS, as do many other companies. Not sure that solves the problem for you, but if there are really thousands of people who need this (and it makes sense that there are at least thousands), then that might be a good product with a real market and might make someone some real money if they can get it working.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2013 at 3:41 pm
Thank you very much to you all for your answers. At least I don't feel like a complete newbie as I can see there is not a straightforward solution to this problem.
I think the same way as GSquared - that this seems to be a problem inside SSMS and since my database is running on windows 2008 server running in English (both the OS server and the SQL Server) I wonder if the results would be the same should the database and OS be in Spanish... maybe I will try this but those being in English is a requirement from our ERP vendor.
January 9, 2013 at 11:10 am
fbernausberraondo (1/8/2013)
Thank you very much to you all for your answers. At least I don't feel like a complete newbie as I can see there is not a straightforward solution to this problem.I think the same way as GSquared - that this seems to be a problem inside SSMS and since my database is running on windows 2008 server running in English (both the OS server and the SQL Server) I wonder if the results would be the same should the database and OS be in Spanish... maybe I will try this but those being in English is a requirement from our ERP vendor.
Yeah, I don't think Microsoft anticipated this particular problem. Might try running SSMS in a VM that's set to Spanish. That might work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2013 at 11:39 am
or you might be able to set up a local / domain user with spanish region settings and just run SSMS as that user using the "runas" command...
not nice, but it might help.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 9, 2013 at 12:10 pm
That's a good one mister.magoo !
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply