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.
36
questions
1
vote
1
answer
520
views
How to format a fixed-length number as a binary string?
I'm trying to wrap my head around how Postgres represents numbers as binary. How would I format a fixed-length number as a string of ones and zeroes?
Example:
select fmt((1<<0)::int2); -- ...
- 63
1
vote
1
answer
788
views
SQL in Access to add leading 0 in front of zip codes
for a project I am working with a table of zip-codes that I imported from a *.csv file, exported form Excel. Those zipcodes all have 5 numbers and are formatted as text within my database. Some of ...
- 11
3
votes
2
answers
22k
views
Why does SQL Server convert floats to scientific notation?
I came across some weird behavior: While passing a float value into a varchar column, the values are getting converted from integers into scientific notation, and it's that scientific notation that ...
- 2,612
-4
votes
1
answer
1k
views
How to separate a number from 3 digits to 3 digits by comma in sql server? [duplicate]
For example, I want to convert the number.
select convert(1354256,money) salMoney
result:
1354256
convert to
1,354,256
4
votes
1
answer
3k
views
Meaning of precision 1, scale 0 in error message?
What datatype does this error message refer to?
ERROR: numeric field overflow
DETAIL: A field with precision 1, scale 0 must round to an absolute value less than 10^1.
I'm getting the error listed ...
- 1,125
2
votes
1
answer
296
views
Microsoft Access: Using other fields in an Autonumber
I'm trying to use Microsoft Access to start an employee database. However, I want the display of the Employee ID to be the initials and then their number. For example, John Smith with the ID 0002 is ...
- 21
1
vote
2
answers
119
views
Best Practices for Separating Content from Presentation in SQL
A common and useful paradigm of programming is to separate content and presentation. One system deals with building the result, another system deals with outputting it.
You see this with HTML vs CSS. ...
- 161
0
votes
1
answer
43
views
Count up numbers from a specific column [closed]
Hi there was wondering if someone could help me out, and can give me a little code example.
i have a table named "projecten", and columns named "pstatus and pafgifte" these columns have numbers only.
...
0
votes
2
answers
76
views
SQL rounding last zeros
I need to store the exact value of a decimal:
For example 0.00500 and 0.0050.
If I use decimal (10, 5), I receive 0.00500 every time. If I enter 0.0050, I still get 0.00500
0
votes
0
answers
464
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 ...
- 223
0
votes
2
answers
1k
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 ...
13
votes
2
answers
23k
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 ...
- 2,612
1
vote
0
answers
909
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 ...
- 177
0
votes
0
answers
3k
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 = ...
- 711
1
vote
1
answer
2k
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-...
- 13
2
votes
1
answer
854
views
Reading decimal mark comma with an external table
SQL Server 2016, SQL Server Management Studio (SSMS). Creating external tables with Transact-SQL and Polybase. How can we read decimals with a decimal mark, comma instead of dot?
When using float and ...
- 139
4
votes
1
answer
2k
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 ...
- 41
11
votes
1
answer
6k
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 ...
- 62.1k
0
votes
2
answers
1k
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
4
answers
10k
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 ...
- 31
0
votes
1
answer
42
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
1
vote
1
answer
89
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 ...
- 607
1
vote
1
answer
297
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'). ...
- 225
5
votes
4
answers
23k
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 ...
- 51
2
votes
1
answer
1k
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:
...
- 111
1
vote
1
answer
5k
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
...
- 113
5
votes
1
answer
4k
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 ...
- 51
0
votes
2
answers
5k
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 ...
- 343
0
votes
1
answer
1k
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, ...
- 329
0
votes
1
answer
438
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 ...
- 131
7
votes
6
answers
185k
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
1
answer
217
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)....
- 121
0
votes
1
answer
97
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-...
- 62.1k
11
votes
2
answers
5k
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 ...
- 833
0
votes
1
answer
603
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,403
8
votes
1
answer
66k
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
- 11.5k