How to convert character data into numeric data in oracle

Oracle database is a very popular relational database management system with rich data types, including numeric types, character types, date types, Blob types, etc. In practical applications, we often encounter the need to convert a character type data into a numeric type. This article will introduce how to convert character data into numeric data in Oracle database.

Oracle database supports several number types, including integer (INT), floating point (FLOAT), double precision (DOUBLE), etc. When we need to convert character data into numeric data, we can use the built-in function TO_NUMBER() of Oracle database to achieve this.

The syntax of the TO_NUMBER() function is as follows:

TO_NUMBER(string, [format], [nls_language])

Among them, string represents the character data that needs to be converted; format Represents the format of the number, which is optional; nls_language represents the locale, which is also optional.

If the format parameter is not specified, the Oracle database will perform conversion according to the numerical format of the current session by default. If the string contains special characters, such as $,,, etc., then you need to use the format parameter to specify the conversion rules.

Example 1:

We first create a table to store character data:

CREATE TABLE test_number (
id NUMBER(10),
data VARCHAR2(50)
);

Then insert some data into the table:

INSERT INTO test_number (id, data) VALUES (1, '123');
INSERT INTO test_number (id, data) VALUES (2, '45.67');
INSERT INTO test_number (id, data) VALUES (3, '-8.9');
INSERT INTO test_number (id, data) VALUES (4, '$123.456');

Next, we use the TO_NUMBER() function to convert character data into numeric data:

SELECT id, TO_NUMBER(data) AS num FROM test_number;

The execution results are as follows:

ID NUM
1 123
2 45.67
3 -8.9
4 123.456

Example 2:

In actual applications, we may encounter the need to convert character data into integer data. In this case, you need to use the format parameter to specify the conversion rules.

For example, we have a table to store student performance data:

CREATE TABLE test_score (
id NUMBER(10),
name VARCHAR2(50),
score VARCHAR2(50)
);

Insert some data into the table:

INSERT INTO test_score (id, name, score) VALUES (1, 'Zhang San', '90' );
INSERT INTO test_score (id, name, score) VALUES (2, '李思', '85.5');
INSERT INTO test_score (id, name, score) VALUES (3, '王五' , '78');
INSERT INTO test_score (id, name, score) VALUES (4, 'Zhao Liu', '60 ');

We can use the following SQL statement to convert the score field into Integer data:

SELECT id, name, TO_NUMBER(score, '999') AS score_int FROM test_score;

In the above SQL statement, '999' represents the format string, where each A 9 represents a single digit. If there are insufficient digits, a 0 is added in front. Therefore, this statement converts character data into integer data.

The execution results are as follows:

ID NAME SCORE_INT
1 Zhang San 90
2 Li Si 85
3 Wang Wu 78
4 Zhao Liu 60

It should be noted that if the string contains non-numeric characters, the TO_NUMBER() function will return an error. Therefore, when using this function, you need to ensure that the string to be converted meets the format requirements of numbers, otherwise you need to use other functions for data cleaning.

Summary:

Oracle database provides the TO_NUMBER() function, which can convert character data into numeric data. When using this function, you need to pay attention to the specification of the conversion rules and ensure that the string to be converted meets the format requirements of numbers. This article introduces two examples and hopes to be helpful to readers.

The above is the detailed content of How to convert character data into numeric data in oracle. For more information, please follow other related articles on the PHP Chinese website!