Questions tagged [number-formatting]

is the process of converting a numeric value into a string suitable for display. Things to take into account are the decimal/thousands separator, digit grouping, the shape of the numbers, how a percentage or currency is displayed, and so on.

Filter by
Sorted by
Tagged with
0
votes
0answers
17 views

Convert BIGINT to hexadecimal (string) in MonetDB

I have a column of type BIGINT in my MonetDB database and for readability I'd like to print its contents as hexadecimal number when executing a SELECTstatement. Is there any way to achieve this? I ...
0
votes
2answers
45 views

display numbers with zero pad

I have some data stored as an unsigned tinyint. When displaying it I want it to be zero padded only if it is 1 digit in length. I have issues with LPAD(CAST(NUMBER AS CHAR CHARSET UTF8),2,'0') when ...
10
votes
2answers
2k views

Best way to put commas into large numbers

I've started a new job and it involves looking at a bunch of big numbers. Is there an easy way to add commas to an int or decimal field to make it readable? For example, SQL Server outputs the column ...
1
vote
0answers
232 views

Why the decimal marker I get is not the one defined in NLS_NUMERIC_CHARACTERS?

According to the Oracle documentation, NLS_NUMERIC_CHARACTERS stores the decimal marker used by the current session. To get the current NLS_NUMERIC_CHARACTERS, I use this request: select value from ...
0
votes
0answers
362 views

SQL Server - What is the RAISERROR substitution parameter for boolean/bit type?

I'd like to quickly dump some context into an error message including some boolean flags using the handy-dandy substitution parameters of RAISERROR such as CREATE PROCEDURE MyProc ( @param1 INT = ...
1
vote
1answer
449 views

Formatting Timestamp in Default Constraint

Can you format a TIMESTAMP WITH TIME ZONE column in a CREATE TABLE default constraint in Postgres v10.x? Example Code: create_date TIMESTAMP WITH TIME ZONE DEFAULT to_char(current_timestamp, 'DD-...
1
vote
0answers
440 views

How do I convert cents retained as integers to dollars as decimals?

I have cent values, retained as integers, that I need to convert to dollars and show them as a decimals. Example A value is kept as 1925, so I need to be able to return 19.25. This is what I had ...
4
votes
1answer
1k views

Why is to_char left padding with spaces?

When ever I use 099 indicating 3 digits 0-padded, I'm getting spaces on the left side. SELECT '>' || to_char(1, '099') || '<'; ?column? ---------- > 001< (1 row) Why is to_char left ...
0
votes
2answers
241 views

DevExpress formats columns differently if they're all numbers

I have a table with a 'Code' column, defined as varchar. However, if you have only numbers in some of the records, for this field, and you search on these records in our application, the results in ...
3
votes
4answers
3k views

Add leading zeros to integers less than 7 digits and ignore anything 7+

I have a query and it almost doing what I want. It is adding leading zeros but when it comes to things 7+ long it turns everything into a 7 number digit. Select RIGHT(rtrim('0000000' + cast(column as ...
0
votes
1answer
27 views

Concatenating time values

I have a problem. In my access database I have two fields with DateTime. I use them for when a meeting starts and ends. I would like to concatenate them for a report, but 09.00 ends up as 9.0 so my ...
1
vote
1answer
50 views

Strange behavior when combining CONVERT(CHAR()) with RIGHT()

When I was trying to create a report that included a decimal-implied number column that needs to be a fixed length of six characters, I chose to go the route of converting to char, replacing the ...
1
vote
1answer
142 views

How to format a number with optional fraction

Is it possible to format a number in Postgresql with optional fraction? Like: 1.0 -> '1' 1.3 -> '1.3' I looked into to_char, but I don't get much further than '1.' with to_char(1.0, 'FM9D9'). ...
4
votes
4answers
7k views

How to format a phone number in a SELECT statement?

I have a database with table name student. I would like to display the register_number and phone_number of the student. The phone_number should be in the following format: +91-123-456-7890 and if ...
2
votes
1answer
621 views

Formatting a Measure in SSAS cube

I have a measure that show number in millions / billions. The formatting of the number for now is like this : "$#,##0.0;-$#,##0.0" I would like to format the number to show something like this: ...
1
vote
1answer
3k views

MySQL - Number format - Separated with Comma

I have a query of MySQL like this- SELECT avg('total_cost') FROM 'employee' And then I am getting output like this- 1254589687.26587952 But I like to have a output like this- 1,25,45,89,687.26 ...
5
votes
1answer
2k views

Storing phone number in E.164

TLDR; If I store a phone number in MySQL(or any DB with equivalent constraints), would the correct format be E.164 values into a VARCHAR(32) field? Have been reading up on format/storage of ...
0
votes
2answers
3k views

change column type from string to numeric

so I have a table with 5.2 million records. 3 columns in this table are for salaries. Those column are currently stored as string and I'd like to change the data type to numeric (I understood while ...
0
votes
0answers
253 views

MySQL - Get actual precision and scale of number

I would like to query the integer part and fractional part of all decimal(8,4) values in a column. By that I mean, I want to know the actual given values, and not the max allowed by the data type, ...
0
votes
1answer
330 views

Numeric Latitude & Longitude values are rounded up when querying SQL Server Database

Developing an application that is using Google Maps. Latitude and longitude values are stored in the table as the correct value. However when they are pulled into a view the value is rounded to the ...
7
votes
6answers
98k views

How do I place leading zeros for numbers less than 10 without affecting those 10+?

Probably a newb question, but... I have a table with a VARCHAR field containing numbers 1 through 300. I need to place a leading zero in front of any numbers less than 10 while retaining the original ...
2
votes
1answer
154 views

Formatting a float value

I am fetching data from MySQL using PHP. I know that most values stored in the database are float. Sometimes, the database outputs values like 3.9999E-5 and sometimes like 0.00104961904762 (14 digits)....
0
votes
1answer
87 views

What do you call numerical coding systems that are left-aligned?

I'm writing an importing mechanism for the NAICS database. I have a few questions about this code-format. I've seen it before and I like it the setup. I'm going to ask some other questions about best-...
9
votes
2answers
4k views

Convert units of measurement

Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes. Unit Conversion Table The unit ...
0
votes
1answer
498 views

how I can create check sum to validate no changes at int string pairs on table

Business case is. 1)Users print document on paper 2)users carry printed paper to Boss, Boss sing on paper 3)after that users going to administrator to digitally sign that paper. My task is to help ...
2
votes
1answer
45k views

MySQL format numbers with comma [closed]

How can I print the integer part of a number with commas separating every three digits? For example, 12345 -> 12,345 1234.567 -> 1,234.567