Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

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
1 answer
15 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 ...
user avatar
  • 1
1 vote
2 answers
3k 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 ...
user avatar
  • 2,504
-4 votes
1 answer
506 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
user avatar
4 votes
1 answer
898 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 ...
user avatar
2 votes
1 answer
61 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 ...
user avatar
  • 21
1 vote
2 answers
70 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. ...
user avatar
  • 161
0 votes
1 answer
41 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. ...
user avatar
0 votes
2 answers
69 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
user avatar
0 votes
0 answers
288 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 ...
user avatar
  • 223
0 votes
2 answers
623 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 ...
user avatar
12 votes
2 answers
18k 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 ...
user avatar
  • 2,504
1 vote
0 answers
738 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 ...
user avatar
  • 167
0 votes
0 answers
2k 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 = ...
user avatar
1 vote
1 answer
1k 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-...
user avatar
2 votes
1 answer
453 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 ...
user avatar
  • 139
4 votes
0 answers
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 ...
user avatar
7 votes
1 answer
4k 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 ...
user avatar
  • 55.8k
0 votes
2 answers
729 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 ...
user avatar
3 votes
4 answers
8k 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 ...
user avatar
  • 31
0 votes
1 answer
30 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 ...
user avatar
  • 1
1 vote
1 answer
60 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 ...
user avatar
1 vote
1 answer
244 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'). ...
user avatar
  • 205
4 votes
4 answers
17k 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 ...
user avatar
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: ...
user avatar
1 vote
1 answer
4k 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 ...
user avatar
5 votes
1 answer
3k 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 ...
user avatar
  • 51
0 votes
2 answers
4k 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 ...
user avatar
  • 303
0 votes
1 answer
372 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, ...
user avatar
  • 299
0 votes
1 answer
402 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 ...
user avatar
  • 131
7 votes
6 answers
166k 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 ...
user avatar
2 votes
1 answer
169 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)....
user avatar
0 votes
1 answer
94 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-...
user avatar
  • 55.8k
10 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 ...
user avatar
0 votes
1 answer
563 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 ...
user avatar
  • 2,323
6 votes
1 answer
62k 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
user avatar
  • 10.3k