format integer to string

Question

I have an integer field in a table and I want to make a query to format the integer value of this field in an char or double field with a especific format.

For example, if my value in the table is 123456 I want to format it as "###.###" what means the result should be like this: 123.456

I've done this using CONCAT function, but the result is not very elegant. I would like to use another funciont spacific for this purpose.

Answer 1

I would suggest doing this in your presentation layer rather than the DB.

This is pretty easy in C#:

// Assuming value is an int
value.ToString("N");

More details on formatting int in various ways see the Microsoft documentation

Answer 2

Maybe you would like to use formatting like '###,###.###' ?

Here is the example.

mysql> select FORMAT( 123446, 4 );

+---------------------+

| FORMAT( 123446, 4 ) |

+---------------------+

| 123,446.0000 |

+---------------------+

1 row in set (0.02 sec)

mysql> select FORMAT( 123446, 0 );

+---------------------+

| FORMAT( 123446, 0 ) |

+---------------------+

| 123,446 |

+---------------------+

1 row in set (0.00 sec)