How to Convert Data Types in Oracle SQL

When using Oracle SQL, there are many ways to convert data types. Three of the most common data types are string (VARCHAR2), number, and date. Let’s take a look at how you can convert to these types.

Converting to a Number in Oracle SQL

To convert a value to a number data type, there are two ways you can do it:

You can use the CAST function or the TO_NUMBER function.

This is one of the most common ways to convert data types in Oracle SQL.

The main difference between CAST and TO_NUMBER is that CAST is ANSI compliant, and TO_NUMBER (along with the other TO_* functions) are Oracle-specific. CAST also works on a wider range of data types, where as there are only a few data types for the TO_* functions.

Let’s start with TO_NUMBER.

TO_NUMBER lets you convert a string (VARCHAR2, CHAR, etc) to a NUMBER type.

TO_NUMBER(value)

The TO_NUMBER function is quite simple. It has one parameter – the value to convert. It accepts one of the string types, and returns a NUMBER data type. For example:

TO_NUMBER('150')

This function would return a number value of 150.

There are also the TO_BINARY_DOUBLE and TO_BINARY_FLOAT functions, which work in a similar way to convert values to a BINARY DOUBLE and BINARY FLOAT data type respectively.

The other way to convert values into a NUMBER data type is to use the CAST function.

CAST (expression AS type_name)

In this function, the expression is the value you want to convert, and the type_name is the data type you’re converting to. You can specify several types here, but NUMBER is the only numeric type you can use.

For example:

CAST('200' AS NUMBER)

This function would return a number value of 200.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

 

Converting to a String in Oracle SQL

As with the NUMBER data type, there are two ways to convert a value to a string data type.

The main string data types in Oracle are:

• CHAR
• NCHAR
• VARCHAR2
• NVARCHAR2
• TEXT

The TO_CHAR function takes a value and converts it to a TEXT data type. It works in a similar way to TO_NUMBER, but has a few parameters.

TO_CHAR(expression, [format,] [options])

The expression is the value to convert to a TEXT data type, which can be a type of date, number, or NTEXT. The format is how the input data is identified or formatted, and options specify languages or currency information.

In most cases, you’ll just need the first parameter, which is the only required parameter. For example:

TO_CHAR('20-JAN-2015')

This will show the date in a TEXT data type.

TO_CHAR(1342)

This will show the number in a TEXT data type.

The other way of converting to string data types is to use CAST. It works in a similar way, but can convert to a few more data types:

• CHAR
• VARCHAR2
• NCHAR
• NVARCHAR2

The CAST function has the same parameters as mentioned earlier.

CAST (expression AS type_name)

In this function, the expression is the value you want to convert, and the type_name is the data type you’re converting to. You can specify several types here, which are from the list above.

For example:

CAST(1094 AS CHAR)

This will convert the value 1094 to a CHAR value.

CAST('01-FEB-2015' AS VARCHAR2)

This will convert the date value to a VARCHAR2 data type.

 

Converting to a Date in Oracle SQL

Just like with the number and string types, there are two ways to convert to a date type.

However, in Oracle, there are two main data types for storing dates:

• DATE – stores the day, month, and year, hour, minute, and second.
• TIMESTAMP – stores the day, month, year, hour, minute, second, and fraction of a second

It depends on what type you want to convert to as to what function to use.

To convert to just a DATE, you can use the TO_DATE function. It takes a few parameters:

TO_DATE(string, [format,] [nls_parameters])

The string is the value to convert, and is the only required parameter. The format is the format of the date stored as a string, and nls_parameters specify language or regional settings.

For example:

TO_DATE('18-APR-2015')

This will convert the value in a string to a DATE type.

Using parameters, you can specify different kinds of dates.

TO_DATE('01/02/2015', 'dd/mm/yyyy')

This will specify the date as being in the format you mentioned, and convert it to a date.

If you don’t specify a time, the default value of 00:00:00 (midnight) is used.

If you want to convert to a date and time, or a TIMESTAMP, you would use the TO_TIMESTAMP function. It works in a similar way.

TO_TIMESTAMP(string, [format,] [nls_parameters])

An example of this is here:

TO_TIMESTAMP('10-MAR-2015 13:06:10')

This will convert the string value to a TIMESTAMP value.

The final way of converting to a date type is to use CAST. Once again, you use CAST and specify the data type to convert to.

CAST (expression AS type_name)

In this function, the expression is the value you want to convert, and the type_name is the data type you’re converting to. You can specify several types here, such as DATE or TIMESTAMP.

For example:

CAST('18-APR-2015' as DATE)

This will convert the string value to a DATE. You can perform a similar function using a TIMESTAMP.

 

Convert Data Types in Oracle SQL

So, there are a few ways to convert between data types. CAST is more versatile as it can handle more data types, but TO_* functions are easier to use as they have fewer parameters.

The functions again are:

• TO_NUMBER
• TO_CHAR
• TO_DATE
• CAST

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: