In this tutorial, we will learn about the MySQL Show
MySQL Syntax of MySQL FORMAT()
Where
Examples of MySQL FORMAT()Let us take a look at a basic example. I have the following number – 15023.2265. Difficult to read at first glance right? Let us format it using the
And the output is, As you can see, we get a formatted number. You can also see that MySQL FORMAT() With Zero Decimal PlacesSuppose you do not want to see decimal places in your formatted number. What can be done? Well, the ‘decimal_places’ argument should be set to 0. The query for it is,
And the output is, MySQL FORMAT() to Add More Decimal Places Than The Original NumberConsider the following number – 230451623.2. Suppose you want the number to be formatted and the resulting number should display 4 decimal places. Wait, but we only have one decimal place in the number so, how does
MySQL FORMAT() With The Locale ArgumentSo far, we were displaying formatted numbers with no ‘locale’ parameter specified. In all the above examples, MySQL assumed ‘locale’ to have the default value ‘en_US’. So for an American, 230,451,623.2 would be easily readable but that wouldn’t be the case for a German or an Indian where a different separator notation is followed between the thousands. Let us display 230451623.2 with such a locale that will follow the German separator notation.
And the output is, The ‘de_DE’ parameter stands for the locale ‘German-Germany’. You can see more locale values here. Let us now display the same number with the locale followed in India. We will use the value ‘en-IN’ meaning ‘English-India’. The query is,
And the output is, FORMAT() With TablesLet us see some examples of Let us format the values of the Salary column using 8statement, aliases, and the 9 function for this. The query is,
And we get the output as, Using the 0 clause and the 1 function, let us find out the sum of salaries by the department by using the similar formatting we used in the previous query.
And the output is, Conclusion
What is the numeric data type in MySQL?MySQL supports all standard SQL numeric data types. These types include the exact numeric data types ( INTEGER , SMALLINT , DECIMAL , and NUMERIC ), as well as the approximate numeric data types ( FLOAT , REAL , and DOUBLE PRECISION ).
How do I format a numeric field in SQL?SQL Format Number Options
Using CAST - SELECT CAST(5634.6334 as int) as number. Using CONVERT - SELECT CONVERT( int, 5634.6334) as number. Using ROUND - SELECT ROUND(5634.6334,2) as number. Using CEILING - SELECT FLOOR(5634.6334) as number.
How to format a number in MySQL?The FORMAT() function formats a number to a format like "#,###,###. ##", rounded to a specified number of decimal places, then it returns the result as a string.
How to display number in MySQL?FORMAT function in MySQL is used to formats the number as a format of "#, ###. ##", rounded it in certain decimal places. After formatting the number, it will return the value as a string.
|