PostgreSQL TO_NUMBER() Data Type Formatting Function

PostgreSQL TO_NUMBER() Data Type Formatting Function

The PostgreSQL TO_NUMBER() function is used to convert a string to a numeric value according to a specified format. This function is essential for transforming formatted text representations of numbers into actual numeric values for accurate calculations and data processing.

---

Syntax

TO_NUMBER(text, format)

The TO_NUMBER() function has the following components:

• text: The string to be converted to a numeric value.
• format: The format to which the string should be converted.

---

Example PostgreSQL TO_NUMBER() Queries

Let's look at some examples of PostgreSQL TO_NUMBER() function queries:

1. Basic TO_NUMBER() Example

SELECT TO_NUMBER('12,454.8-', '99G999D9S') AS numeric_value;

This query converts the string '12,454.8-' to a numeric value in the format '99G999D9S', resulting in -12454.8.

2. TO_NUMBER() with Different Numeric Format

SELECT TO_NUMBER('1,234,567.89', '9,999,999.99') AS numeric_value;

This query converts the string '1,234,567.89' to a numeric value in the format '9,999,999.99', resulting in 1234567.89.

3. TO_NUMBER() with Column Values

SELECT id, TO_NUMBER(price_text, '99G999D9') AS price
FROM products;

This query retrieves the id and the price converted from the text representation in '99G999D9' format for each row in the products table.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the TO_NUMBER() function to convert text to numeric values.

Step 1: Creating a Table

This step involves creating a new table named products to store product data, including their prices as text.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price_text TEXT
);

In this example, we create a table named products with columns for id, name, and price_text.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the products table.

INSERT INTO products (name, price_text)
VALUES ('Product A', '1,234.56'),
       ('Product B', '7,890.12'),
       ('Product C', '3,456.78');

Here, we insert data into the products table.

Step 3: Using the TO_NUMBER() Function

This step involves using the TO_NUMBER() function to convert the text price values to actual numeric values in the products table.

Convert text price to numeric value:

SELECT id, name, TO_NUMBER(price_text, '9G999D99') AS price
FROM products;

This query converts the text price values to numeric values in the format '9G999D99'.

---

Conclusion

The PostgreSQL TO_NUMBER() function is a fundamental tool for transforming formatted text representations of numbers into actual numeric values. Understanding how to use the TO_NUMBER() function and its syntax is essential for accurate calculations and data processing in PostgreSQL databases.