Insert Non-English Decimal Points in MySQL

Insert non-English decimal points in mysql

One of the reasons you can't do that is that the comma , is used for separating field-values in INSERT statements.

INSERT INTO a(b,c) VALUES (3,4,5) would be ambiguous.

Should it result into b=3.4 , c=5 or b=3 , c=4.5 ?

Change decimal separator in MySQL

No, you can't. That's the SQL standard and MySQL complies with it (in that point at least).

The problem is not really with output (as you mention, there are various FORMAT functions in most DBMSs) but with INSERT. If you could use comma , for example as decimal point (that's common in other locales) which is aslo used as values separator, Inserts would become ambiguous. See my answer in the question: insert-non-english-decimal-points-in-mysql

Insert decimal with comma is interpreted as two values

To specify a number as a constant in a SQL script, you should follow SQL syntax rules, not locale settings. And the syntax dictates that you use a . as a decimal separator. As for thousand separators, they are not used at all.

Note that this is strictly about coding your data in a script, not displaying them. Displaying is where your locale settings do matter, and if they are in order you should get your output formatted accordingly: decimal separators as commas, thousand separators as periods.

Convert decimal to euro currency in MYSQL

The documentation reveals that FORMAT() has up to three arguments (emphasis mine):

FORMAT(X,D[,locale])

Formats the number X to a format like '#,###,###.##', rounded to D
decimal places, and returns the result as a string. If D is 0, the
result has no decimal point or fractional part.

The optional third parameter enables a locale to be specified to be
used for the result number's decimal point, thousands separator, and
grouping between separators. Permissible locale values are the same as
the legal values for the lc_time_names system variable (see Section
10.7, “MySQL Server Locale Support”). If no locale is specified, the default is 'en_US'.

That could be a starting point. (You apparently want the format provided by the de_DE locale.)

Demo

Issues with POST and PUT in table with comma separated numeric values

As pointed out by in the comments to my question, using a parameterized query solved the issue with the added benefit of securing the SQL query. While DubDub's answer was an okay workaround, the following solves it in a more scalable, secure way.

The SQL query now looks like this:

 string sql = "UPDATE coordinate SET longitude = @Longitude, latitude = @Latitude, measurementId = @MeasurementId WHERE id = @ID";

 List<MySqlParameter> parameters = SqlFactory.CreateParametersFor(coordinate);

 cmd = SqlFactory.CreateParameterizedQuery(sql, parameters, conn);

 cmd.Parameters.Add(ID);

 await cmd.ExecuteNonQueryAsync();

Where the CreateParametersFor() and CreateParameterizedQuery() are implemented like so:

public static MySqlCommand CreateParameterizedQuery(string sql, List<MySqlParameter> parameters, MySqlConnection connection)
    {
        MySqlCommand cmd = new MySqlCommand(sql, connection);
        foreach (MySqlParameter parameter in parameters) cmd.Parameters.Add(parameter);
        return cmd;
    }

    public static List<MySqlParameter> CreateParametersFor(Coordinate coordinate)
    {
        List<MySqlParameter> parameters = new List<MySqlParameter>();

        MySqlParameter latitude = new MySqlParameter()
        {
            ParameterName = "@Latitude",
            Value = coordinate.Latitude
        };

        MySqlParameter longitude = new MySqlParameter()
        {
            ParameterName = "@Longitude",
            Value = coordinate.Longitude
        };

        MySqlParameter measurementId = new MySqlParameter()
        {
            ParameterName = "@MeasurementId",
            Value = coordinate.MeasurementId
        };

        parameters.Add(latitude);
        parameters.Add(longitude);
        parameters.Add(measurementId);

        return parameters;
    }

mysql sum() does not give correct decimal value

Select cast(sum(atotal) as decimal(12,2)) AS itemstotal FROM mytable

---

Related Topics