SQL to_char

The to_char() function in SQL allows you to convert a value into a string. This function is available in Oracle databases.

Let us discuss how you can use Oracle’s to_char() function and the available alternatives in other database engines.

Oracle to_char() Function

The to_char() function allows you to convert a number or a datetime object into a string in Oracle databases.

The available syntaxes are as shown below:

The function will take a number in the first syntax and convert it into a varchar2 type. The number specified in this case can be type number, binary_float, or binary_double.

We can illustrate an example as shown below:

The above example will convert the number 1000 to a string specified in the fmt. You can check Oracle formatting models to learn more.

The code above should return output as:

You can also convert a number into a string with zeros as shown in the example below:

The above should return:

To_char – Datetime

The second use of the to_char function in Oracle is to convert a DateTime into a string. The function takes the DateTime object of type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, etc.

The function will then convert the specified value into a VARCHAR2 type.

We can illustrate an example usage as shown below:

The above example should convert the value from the sysdate function into a string following the specified format of YYYY-MM-DD.

The output is as shown below:

You can specify other formatting options as shown in the resource below.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34510

For example, to convert the above value into long date format, we can run:

The code above should return:

SQL Cast Function

You will notice that other database engines do not provide the to_char function. However, we can use the cast() from standard SQL to convert a value into a string.

The function syntax is as shown:

The cast function will then allow you to convert a value to a string type. So, for example, to convert a number to a string, we can do:

The code above should convert the integer into varchar as shown:

If the specified cannot be converted to the target type, the function will return an error.

An example is as shown:

In the above example, we attempt to convert the string hi to an integer. Unfortunately, this will result in an error as shown below:

ERROR: invalid input syntax for type integer: “Hi!”

NOTE: The error message may differ depending on your target database engine.

You can also use the cast function to convert a DateTime object into a string. Consider the illustrated example shown below:

The above query should return:

As you can see, the cast function converts the value of the now() function into a string.

Conclusion

For this article, you learned how to use the to_char() function to convert a number or DateTime into a string using the Oracle database engine. We also covered how to convert from one type to another using the cast() function in standard SQL.

Thank you for reading!!