How to perform common data transformations in Oracle queries

Oracle is one of the most popular relational database management systems in the world. Its query language, SQL, is one of the must-have skills used by most database administrators and developers. However, in Oracle queries, sometimes we need to do some data conversion, such as converting uppercase letters to lowercase letters or vice versa. In this article, we will discuss how to perform common data transformations in Oracle queries.

1. Convert letters to upper and lower case

Sometimes we need to convert the uppercase and lowercase letters in a column. This can be done by using Oracle's built-in LOWER and UPPER functions. accomplish. The LOWER function converts uppercase letters to lowercase letters, and the UPPER function converts lowercase letters to uppercase letters. For example, the following statement will convert the case of the last_name column in the employees table:

SELECT UPPER(last_name) AS UPPER_LAST_NAME, LOWER(last_name) AS LOWER_LAST_NAME
FROM employees;

2. Convert date format

In Oracle, dates can have many different formats Format. Sometimes we need to convert dates from one format to another, such as converting from "YYYY-MM-DD" to "MM/DD/YYYY". To achieve this goal, we can use the TO_CHAR function. The TO_CHAR function is used to convert another data type (such as date) to a character type. The following is an example of converting the hire_date date column in the employees table from "YYYY-MM-DD" format to "MM/DD/YYYY" format:

SELECT employee_id, first_name, TO_CHAR(hire_date,'MM/DD/YYYY') AS HIRE_DATE
FROM employees;

3. Convert numbers

Sometimes we need to convert numbers to different formats, such as converting integers to decimals or retaining numbers to a few decimal places. To achieve this goal, we can use the TO_CHAR function and TO_NUMBER function. The following is an example of retaining two decimal places in the salary column in the employees table:

SELECT employee_id, first_name, TO_CHAR(salary, '99999.99') AS SALARY
FROM employees;

In the above statement, '99999.99' is a format template that specifies what format the number should be. The TO_CHAR function converts a number to character type and uses a format template to format the number into a string. On the other hand, if we need to convert a string type number to numeric type, we can use the TO_NUMBER function. The following is an example of converting the string '12.34' to a numeric type:

SELECT TO_NUMBER('12.34') AS NUMBER_VALUE
FROM dual;

4. Use CASE statement for conversion

Sometimes we need to convert data based on certain conditions Convert. This can be achieved using the CASE statement. The CASE statement allows us to select different values ​​based on whether a condition is met. The following is an example of using CASE statement to convert salary column to level in employees table:

SELECT employee_id, first_name, salary,
    CASE 
        WHEN salary >= 10000 THEN 'High Salary'
        WHEN salary >= 5000 AND salary < 10000 THEN 'Mid Salary'
        ELSE 'Low Salary'
    END AS SALARY_LEVEL
FROM employees;

In the above statement, we use CASE statement to convert salary column to level. If the salary is greater than or equal to 10000, the level is 'High Salary'; if the salary is greater than or equal to 5000 but less than 10000, the level is 'Mid Salary'. Otherwise, the level is 'Low Salary'.

In this article, we discussed some methods for common data transformations in Oracle queries. These techniques are basic skills in Oracle queries and are widely used in various scenarios. Let's keep these techniques in mind so that we can process and transform data more efficiently in real-world projects.

The above is the detailed content of How to perform common data transformations in Oracle queries. For more information, please follow other related articles on the PHP Chinese website!