How to Convert Number to String in Oracle?

In Oracle, you can convert number to string using the TO_CHAR() function, however, you can simply assign a numeric value to a string variable without using any function in Oracle. Below are the examples.

1. Convert Number to String using TO_CHAR() Function

PL/SQL Program

declare
   s_phone varchar2(100);
begin
   s_phone := TO_CHAR(9876543210);
   dbms_output.put_line(s_phone);
end;

In this program, we are declaring a variable called "s_phone" as a string of characters, specifically a varchar2 type that can hold up to 100 characters.

Next, we have the "begin" statement, which marks the beginning of the program logic.

Inside the program, we assign a value to the variable "s_phone" using the TO_CHAR function. The TO_CHAR function converts a number to a character string. In this case, the number being converted is 9876543210, which is a 10-digit phone number.

After assigning the converted value to the variable "s_phone", we use the "dbms_output.put_line" statement to display the value of "s_phone" on the output screen.

So, when you run this program, it will convert the phone number 9876543210 into a string and then display it on the screen using the "dbms_output.put_line" statement.

Output:

9876543210

Convert in currency format:

declare
   s_dollar varchar2(100);
begin
   s_dollar := TO_CHAR(1234, '$999999.99');
   dbms_output.put_line(s_dollar);
end;

Output:

$1234.00

2. Directly assign a Number to a String (Varchar2) Variable

declare
   s_pincode varchar2(100);
begin
   s_pincode := 123456789;
   dbms_output.put_line(s_pincode);
end;

In this PL/SQL program, we declare a variable called "s_pincode" as a string of characters, specifically a varchar2 type that can hold up to 100 characters.

Next, we have the "begin" statement, which marks the beginning of the program logic.

Inside the program, we assign a value to the variable "s_pincode" using the assignment operator (:=). The value assigned is the number 123456789.

After assigning the value to the variable, we use the "dbms_output.put_line" statement to display the value of "s_pincode" on the output screen.

So, when you run this program, it will assign the number 123456789 to the variable "s_pincode" and then display it on the screen using the "dbms_output.put_line" statement.

Output:

123456789

3. Get Number into String (Varchar2) Variable using SQL Query

declare
   s_amount varchar2(100);
begin
   select 98765.99 into s_amount from dual;
   dbms_output.put_line(s_amount);
end;

In this PL/SQL program, we declare a variable called "s_amount" as a string of characters, specifically a varchar2 type that can hold up to 100 characters.

Next, we have the "begin" statement, which marks the beginning of the program logic.

Inside the program, we have a SELECT statement that retrieves a value from the "dual" table. The value being selected is 98765.99, which is a decimal number.

We use the INTO clause to specify that the selected value should be stored in the variable "s_amount".

After executing the SELECT statement and storing the value in "s_amount", we use the "dbms_output.put_line" statement to display the value of "s_amount" on the output screen.

So, when you run this program, it will retrieve the value 98765.99 from the "dual" table and store it in the variable "s_amount". Finally, it will display the value of "s_amount" on the screen using the "dbms_output.put_line" statement.

Output:

98765.99

4. Convert Number to String using TO_CHAR() Function in SQL

declare
   s_amount varchar2(100);
begin
   select to_char(98765.99, '$99999999.99') into s_amount from dual;
   dbms_output.put_line(s_amount);
end;

In this PL/SQL program, we declare a variable called "s_amount" as a string of characters, specifically a varchar2 type that can hold up to 100 characters.

Next, we have the "begin" statement, which marks the beginning of the program logic.

Inside the program, we have a SELECT statement that includes a conversion function. The function being used is "to_char", which converts a number to a character string. The number being converted is 98765.99, and the format specified is '$99999999.99'.

We use the INTO clause to specify that the converted value should be stored in the variable "s_amount".

After executing the SELECT statement and storing the converted value in "s_amount", we use the "dbms_output.put_line" statement to display the value of "s_amount" on the output screen.

So, when you run this program, it will convert the number 98765.99 into a formatted string with a dollar sign and specific number format. Then, it will store the converted value in the variable "s_amount" and display it on the screen using the "dbms_output.put_line" statement.