Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

oracle sql query to format number by comma

Tags:

Guys i want to print the following data with comma separated values using oracle sql

696585242087   to   69,658,524,2087 

and same for the decimal.

like image 662
Dead Programmer Avatar asked Jan 24 '11 15:01

Dead Programmer


People also ask

How do I format numbers in SQL?

MySQL FORMAT() Function 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.

Can you add commas to numbers in SQL?

We can use the FORMAT() function to format numbers with commas. When we use this function, we pass the number and a format string. The format string determines how the number will be formatted when returned. The FORMAT() function returns a formatted string representation of the number, based on the values we provide.

How do I change the decimal separator in Oracle?

To switch dots and commas, change the NLS_NUMERIC_CHARACTERS: create table t ( x number ); insert into t values (1234.56); alter session set NLS_NUMERIC_CHARACTERS = '.,'; select * from t; alter session set NLS_NUMERIC_CHARACTERS = ',. '; select * from t; Note this setting is in the client.

How do I add a comma to a number in Oracle?

When you use the TO_CHAR () function to format a number in Oracle, you use a format model to determine how the number should be formatted. For example, you could format a number like 12,345.00 or like 12.345,00, depending on your locale. The format model can include the G or D format elements to add a comma to a number.

How to format numbers with commas in SQL Server?

In SQL Server we can use the FORMAT () function to format numbers with commas. This function accepts the number and a format string: SELECT FORMAT (123456.789, 'N') AS "Number", FORMAT (123456.789, 'P') AS "Percent", FORMAT (123456.789, 'C') AS "Currency";

How to use number format in Oracle?

Oracle number format You can use a number format in Oracle in : 1. The TO_CHAR function to format a number datatype. i.e. TO_CHAR(value,'90.99') 2. The TO_NUMBER function to convert a CHAR or VARCHAR2 value to a NUMBER datatype. i.e. TO_CHAR('24.33','99.99') All number format models cause the number to be rounded to the specified number of ...

How to change the format of a number in SQL?

We can use the SQL CAST function to change the format of the number as follows: The SQL CONVERT function can do the same things as CAST. It has different syntax and in some scenarios, it has additional options. The following table shows some examples like the ones used for CAST.


1 Answers

SELECT  TO_CHAR(696585242087, '99G999G999G9999', 'NLS_NUMERIC_CHARACTERS=",."') FROM    dual 
like image 65
Quassnoi Avatar answered Oct 04 '22 01:10

Quassnoi