escape character for decimal point

escape character for decimal point by MladenP

Closed

as Won't Fix Help for as Won't Fix

Type:	Suggestion
ID:	126363
Opened:	3/7/2006 3:24:32 AM
Access Restriction:	Public

Workaround(s)

view

When building a query in the code (not recommended but sometimes there's no choice) without parameters decimal values are equally problematic as dates with different cultures. Datetime solves this problem very nicly with yyyymmdd format.
If there was a similar solution for decimal separator it would be great.
because english decimal separator is a dot (100.56) in slovenia (my country) is a comma (100,56).
so by doing
insert into MyTable (DecimalColumn) values('12,2')
the sql server returs an error.

Details (expand)

English

SQL Server 2005 - Enterprise Edition (32)

SQL Engine

Windows Server 2003

Slovenian

it would be great if i could do simply:
insert into MyTable (DecimalColumn) values('12~2') where ~ (or any other char) is a culture nonspecific decimal separator that would work only in update or insert.
that would solve a lot of problems wit old apps and maybe a few new one.
not everyone know that parameteres should be used.

Faster Development

Improved User Interface

Faster Development

0 attachments

Posted by Microsoft on 3/10/2011 at 2:23 PM

Hello,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”.

We will look at adding culture aware functions in a future release that will allow you to perform conversions like you want.

--
Umachandar, SQL Programmability Team

Posted by Hilarion on 5/18/2007 at 1:17 PM

I'm also from a country that uses comma as decimal separator, but I fail to see the problem. There IS a universal decimal separator in SQL, it's a dot. You can do:
INSERT INTO MyTable (DecimalColumn) VALUES( 12.2 )
and it will always work OK regardless of regional settings (or any other settings).
If the problem is with conversion from string data to numeric data, then the solution would be to implement a conversion function, that would accept not only a string value to be converted, but also a string (single character?) value containing used decimal separator. For example in Oracle there is TO_NUMBER conversion function, which can be given from 1 to 3 parameters. The first one is a value to be converted, second one is the format used (which can provide such info as thousand separator usage or hexadecimal format) and third can contain national settings to be used in conversion (which include decimal and thousand separator).

Posted by Microsoft on 3/13/2006 at 11:16 AM

Hi, thanks for the feedback. We'll look into how we can provide something like this in future releases of SQL Server.