number format with MySQL

Question

hey i need a way to get a formated number from my column decimal 23 2  NOT NULL DEFAULT  0 00   in php i could use this function number format  1111 00   2             it would return 1 111 00  in Germany we use   to define decimal numbers   how would i do this in mysql  with string replaces

User · Accepted Answer

With performance penalty and if you need todo it only in SQL you can use the  FORMAT function and 3 REPLACE   After the format replace the   with another char for example    then replace the   with a   and then the chararacter you choose by a   which lead you for your example to 1 111 00  SELECT REPLACE REPLACE REPLACE FORMAT  1111 00   2

User · Answer

You need this   CONCAT REPLACE FORMAT number 0               SUBSTRING INDEX FORMAT number 2      -1

User · Answer

At least as far back as MySQL 5 5 you can use format   SELECT FORMAT 123456789 123456789 2      produces 123 456 789 12     SELECT FORMAT 123456789 123456789 2  de DE            produces 123 456 789 12     note the swapped   and   for the de DE locale  German-Germany        From the MySQL docs   https   dev mysql com doc refman 5 5 en string-functions html function format  Available locales are listed elsewhere in the docs  https   dev mysql com doc refman 5 5 en locale-support html

User · Answer

http   blogs mysql com peterg 2009 04   In Mysql 6 1 you will be able to do FORMAT X D   locale name     As in    SELECT format 1234567 2    de DE        For now this ability does not exist  though you MAY be able to set your locale in your database my ini check it out

User · Answer

You can use   SELECT round 123 4566 2   - gt  123 46

User · Answer

Antonio s answer  CONCAT REPLACE FORMAT number 0               SUBSTRING INDEX FORMAT number 2      -1     is wrong  it may produce incorrect results   For example  if  number  is 12345 67  the resulting string would be    12 346 67    instead of   12 345 67    because FORMAT number 0  rounds  number  up if fractional part is greater or equal than 0 5  as it is in my example    What you COULD use is  CONCAT REPLACE FORMAT FLOOR number  0               SUBSTRING INDEX FORMAT number 2      -1     if your MySQL MariaDB s FORMAT doesn t support  locale name   see MindStalker s post - Thx 4 that  pal   Note the FLOOR function I ve added

User · Answer

FORMAT X D  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     SELECT FORMAT 12332 123456  4           -   12 332 1235

User · Answer

CREATE DEFINER  yourfunctionname      FUNCTION  money         betrag  DECIMAL 10 2       RETURNS varchar 128  CHARSET latin1  LANGUAGE SQL  NOT DETERMINISTIC  CONTAINS SQL  SQL SECURITY DEFINER  COMMENT     return       select replace format cast betrag as char  2           as betrag      will creating a MySql-Function with this Code    select replace format cast amount as char  2           as amount formated

[mysql] number_format() with MySQL

Examples related to mysql