oracle convert to string

In Oracle database, sometimes it is necessary to convert numeric or date data into string type to facilitate output or string splicing operations. This article will introduce how to convert numeric, date and Boolean data into string types in Oracle database.

1. Convert numeric type to string type

1. TO_CHAR function

In Oracle database, the most commonly used method is to convert numeric type to string type. The type of function is the TO_CHAR function, and its syntax is as follows:

TO_CHAR(number, [format], [nls_language])

Among them, number represents the numerical type to be converted, and format represents formatting The template can be a preset format or a custom format. nls_language represents the locale of the data. If this parameter is omitted, the default value of the current session is used. The following are some commonly used formatting templates:

Template Description
FM Delete leading spaces
0 Fill the missing digits with 0
09 Fill the missing digits with 0s, two-digit alignment
9999 Insufficient digits are filled with spaces, aligned to four digits
$9,999 Signed amount, grouped by three digits
Dollar9,999.99 Signed amount, thousands separator and two decimal places

For example, put the number 12345.67 is converted into a string type, the format is currency format, and the US English language environment is used. You can use the following code:

SELECT TO_CHAR(12345.67, '$9,999.99', 'NLS_LANGUAGE=AMERICAN')
FROM dual;

The output result is: $12,345.67.

2. CAST function

In addition to the TO_CHAR function, Oracle also provides the CAST function for converting numeric types to string types. The syntax is as follows:

CAST(expression AS data_type)

Among them, expression represents the numerical type to be converted, and data_type represents the target data type to be converted. Here, we can convert the numeric type to CHAR or VARCHAR2 type. For example, to convert the number 12345.67 into a string type, you can use the following code:

SELECT CAST(12345.67 AS VARCHAR2(10))
FROM dual;

The output result is: 12345.67.

2. Convert date type to string type

1. TO_CHAR function

In Oracle database, convert date type data to string type in the same way You can use the TO_CHAR function, its syntax is as follows:

TO_CHAR(date, [format], [nls_language])

Among them, date represents the date data to be converted, and format represents the formatted Template, nls_language represents the locale of the data. The following are some commonly used formatting templates:

Template description
YYYY-MM-DD The year, month and day of the date
YYYY-MM-DD HH24:MI:SS The year, month of the date , day, hour, minute and second
MONTH The full name of the month (such as January)
MON The abbreviation of the month (such as Jan)
DD-MON-YYYY The day, month and year of the date (such as 31- JAN-2022)

For example, to convert date type data '2022-01-31' to string type, you can use the following code:

SELECT TO_CHAR('2022-01-31' , 'YYYY-MM-DD')
FROM dual;

The output result is: 2022-01-31.

2. CAST function

In addition to the TO_CHAR function, Oracle also provides the CAST function for converting date type data to string type. The syntax is as follows:

CAST(expression AS data_type)

Among them, expression represents the date data to be converted, and data_type represents the target data type to be converted. Here, we can convert date type data to CHAR or VARCHAR2 type. For example, to convert date type data '2022-01-31' to string type, you can use the following code:

SELECT CAST('2022-01-31' AS VARCHAR2(10))
FROM dual;

The output result is: 2022-01-31.

3. Conversion of Boolean type to string type

In Oracle database, Boolean type data cannot be directly converted to string type, but Boolean type data can be converted with the help of CASE expression. The following is a sample code that converts Boolean data to string type:

SELECT

CASE WHEN flag = 'true' THEN 'Yes'
     WHEN flag = 'false' THEN 'No'
END AS result

FROM my_table;

where flag is Boolean data, 'true' and 'false' are string types, and result is the converted string type. If the flag is 'true', it returns 'Yes'; if the flag is 'false', it returns 'No'.

To sum up, this article introduces the method of converting numeric, date and Boolean data into string types in Oracle database. For numeric and date data, we can use the TO_CHAR function or CAST function to achieve conversion; for Boolean data, we can use CASE expressions to achieve conversion.

The above is the detailed content of oracle convert to string. For more information, please follow other related articles on the PHP Chinese website!