Convert Number to String with Leading Zeros in Oracle

In Oracle, you might encounter situations where you need to convert a number into a string with leading zeros. This can be especially useful when dealing with data that requires a specific format, such as employee IDs or product codes. In this tutorial, we will explore different ways to achieve this conversion in Oracle SQL.

How to Convert a Number to a String with Leading Zeros in Oracle

Using the TO_CHAR Function

Oracle provides the TO_CHAR function, which allows you to convert a number into a string with various formatting options, including leading zeros. Here's how you can do it:

SELECT TO_CHAR(your_number, 'FM00000') AS formatted_number
FROM your_table;

Explanation:

• TO_CHAR is a function used for converting data types into character strings.
• your_number should be replaced with the number you want to convert.
• 'FM00000' is a format model that specifies the desired format. The 'FM' prefix removes leading and trailing spaces.

Example 1:

Let's say you have a number, 9, and you want to convert it into a string with leading zeros to make it five digits long. Using the TO_CHAR function:

SELECT TO_CHAR(9, 'FM00000') AS formatted_number FROM dual;

The result will be:

Example 2:

If you have a larger number, such as 12345, and you want to format it with leading zeros, you can still use the same TO_CHAR function:

SELECT TO_CHAR(12345, 'FM00000') AS formatted_number FROM dual;

The result will be:

Padding with LPAD Function to Convert a Number to a String with Leading Zeros in Oracle

Another way to achieve the same result is by using the LPAD function, which stands for "left pad." It allows you to pad a string (in this case, a number converted to a string) with a specified character (zero in our case) to reach a desired length.

SELECT LPAD(your_number, 5, '0') AS formatted_number FROM your_table;

Explanation:

• LPAD is a function that pads the left side of a string with a specified character until it reaches the desired length.
• your_number should be replaced with the number you want to convert.
• 5 is the total desired length of the string (including leading zeros).
• '0' is the character used for padding.

Example 1 (Using LPAD):

Let's revisit the first example, where we have the number 9, and we want to convert it into a string with leading zeros to make it five digits long using the LPAD function:

SELECT LPAD(9, 5, '0') AS formatted_number FROM dual;

The result will be:

Example 2 (Using LPAD):

For a larger number like 12345, you can use the LPAD function as well:

SELECT LPAD(12345, 8, '0') AS formatted_number FROM dual;

The result will be:

Conclusion

Converting a number to a string with leading zeros in Oracle is straightforward and can be achieved using either the TO_CHAR function or the LPAD function. You can choose the method that suits your specific needs and coding style. Both approaches will give you the desired result, making your data more consistent and easier to work with in various applications.

Now, you have the knowledge to format numbers with leading zeros in Oracle SQL, helping you manage your data effectively.

Ref: Oracle To_Char Function