What should I do? Select int as currency or convert int to currency format in MySql?

MySQLMySQLi Database

To convert int to current format, use CONCAT() with FORMAT() function from MySQL.

The syntax is as follows −

SELECT CONCAT(‘CurrencySymbol’,FORMAT(yourColumnName,valueAfterDecimal)) as AnyVariableName from yourTableName;

To understand the above syntax, let us create a table. The query to create a table −

mysql> create table AddingCurrencySymbolDemo
   −> (
   −> Amount int
   −> );
Query OK, 0 rows affected (1.50 sec)

Insert records in the table using insert command. The query is as follows −

mysql> insert into AddingCurrencySymbolDemo values(250);
Query OK, 1 row affected (0.22 sec)

mysql> insert into AddingCurrencySymbolDemo values(500);
Query OK, 1 row affected (0.22 sec)

mysql> insert into AddingCurrencySymbolDemo values(1000);
Query OK, 1 row affected (0.16 sec)

mysql> insert into AddingCurrencySymbolDemo values(750);
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from AddingCurrencySymbolDemo;

The following is the output −

+--------+
| Amount |
+--------+
|    250 |
|    500 |
|   1000 |
|    750 |
+--------+
4 rows in set (0.00 sec)

Here is the query to convert int to currency format. The query is as follows −

mysql> select concat('$',format(Amount,0)) as AddedCurrency from AddingCurrencySymbolDemo;

The following is the output displaying current format −

+---------------+
| AddedCurrency |
+---------------+
| $250          |
| $500          |
| $1,000        |
| $750          |
+---------------+
4 rows in set (0.00 sec).
raja
Published on 16-Jan-2019 08:24:55
Advertisements