Truncating and Formatting Number Fields for Select Queries

When working with numerical data in a database, it’s common to need to format or truncate specific fields to meet the requirements of a select query. In this article, we’ll explore how to achieve this by dividing the numbers by 1000, rounding down to the nearest integer using the floor() function, and concatenating a fixed string value if needed.

Background on Number Formats

In most databases, including Oracle, SQL Server, and PostgreSQL, number fields are stored as strings. This means that even though you might be working with numbers in your queries, the actual storage format is still a string of digits. When performing arithmetic operations on these strings, the results can be unexpected.

To illustrate this issue, let’s look at an example using the sample data provided:

-- sample data as a CTE
with country (loctn, area, people) as (
  select 'abc', 12345, 153465 from dual
  union all select 'cxv', 43566, 388573 from dual
  union all select 'xyz', 134567 , 1234567  from dual
)

As we can see, the area values are integers in the hundreds of thousands. If we perform a simple arithmetic operation on these numbers without proper formatting, the results can be misleading.

Truncating Numbers with Floor()

To address this issue, we’ll use the floor() function to round down the numbers to the nearest thousand. This will give us the desired truncated values.

For example, let’s take the area value of 12345 and divide it by 1000 using the floor() function:

select floor(area / 1000) as area
from country
where loctn = 'abc';

This would result in an output like this:

As we can see, the area value has been successfully truncated to the nearest thousand.

Formatting Numbers with Concatenation

In addition to truncating numbers, we might also need to format them by concatenating a fixed string value. For example, if we want to display the people values in thousands of individuals (e.g., 153K+), we can use the || operator to concatenate a fixed string.

Let’s take the same area value of 12345 and format it as follows:

select floor(area / 1000) || 'K+' as area,
       floor(people / 1000) || 'K+' as people
from country
where loctn = 'abc';

This would result in an output like this:

As we can see, the area and people values have been successfully formatted to display thousands.

Handling Multiple Values

In some cases, you might need to apply these formatting rules to multiple values. This is where a CTE (Common Table Expression) comes in handy.

Let’s take our sample data and create a CTE that applies the same formatting rules to all three rows:

with formatted_country as (
  select 'abc', 12345, 153465 from dual
  union all select 'cxv', 43566, 388573 from dual
  union all select 'xyz', 134567 , 1234567  from dual
)
select loctn,
       floor(area / 1000) || 'K+' as area,
       floor(people / 1000) || 'K+' as people
from formatted_country;

This would result in an output like this:

As we can see, the same formatting rules have been applied to all three rows.

Real-World Example

Now that we’ve covered the basics of truncating and formatting numbers, let’s take a look at a real-world example. Suppose we’re working with a database that stores sales data, including the total revenue and number of customers for each product.

We want to display the revenue values in thousands of dollars and customer counts in thousands of individuals. We can use the following query:

with formatted_sales as (
  select 'Product A', 100000, 5000 from dual
  union all select 'Product B', 200000, 7500 from dual
  union all select 'Product C', 50000, 2500  from dual
)
select product,
       floor(revenue / 1000) || 'K' as revenue,
       floor(customers / 1000) || 'K' as customers
from formatted_sales;

This would result in an output like this:

As we can see, the revenue and customer counts have been successfully formatted to display thousands.

Conclusion

In this article, we’ve explored how to truncate and format numbers using the floor() function and concatenation. We’ve also discussed the importance of formatting numbers when working with select queries. By following these tips and techniques, you can ensure that your data is displayed in a clear and concise manner.

Whether you’re working with Oracle, SQL Server, or PostgreSQL, the principles of truncating and formatting numbers remain the same. So next time you encounter this challenge, remember to reach for your floor() function and concatenation operator to get the job done!

Last modified on 2024-05-07