How to Extract Pure Integer Values from a Column in Oracle without regexp_like

Learn how to filter out and retrieve only integer values from a column in Oracle SQL, even without using `regexp_like`.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to get integer from column without using regexp_like

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Extract Pure Integer Values from a Column in Oracle without regexp_like

When working with data in SQL, there may be times when you want to extract specific types of values from a column. For instance, if you have a column containing mixed values that include both numbers and letters, you might find yourself in need of a way to filter out only those entries that contain pure numeric values.

In this guide, we’ll tackle the problem of retrieving only the integer values from a column in Oracle, particularly when you cannot use the regexp_like function—an essential feature in more recent versions of Oracle SQL.

The Problem

You have a column (let’s call it X) in Oracle that can contain a variety of strings, such as:

a1b2c3

abc

1ab

123

156-346

Your goal is to construct an SQL query that returns those entries in column X which contain pure integer values, such as 123 or 456, without relying on regexp_like due to compatibility issues with Oracle version 10g.

The Solution

There are two functional approaches to achieve this:

1. Using the regexp_replace Function

While regexp_like is not available in Oracle 10g, you can use the regexp_replace function to filter characters and isolate numbers. The following SQL code demonstrates this method:

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

The regexp_replace function takes each value in the column and replaces any character that is not a digit (from 0 to 9) with an empty string.

The resulting output removes all non-numeric characters, allowing you to see what integer values would look like.

2. Utilizing the translate Function

Another reliable method to extract numeric values is through the translate function. Here’s how you can do it:

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

This method involves two translate function calls. The inner translate replaces digits with a special character ($ in this case) while keeping the digits intact.

The outer translate then removes the $ symbol and, by default, any non-digit character is effectively filtered out.

Conclusion

By using the methods outlined above, you can effectively retrieve pure integer values from a column in Oracle SQL, even without access to the regexp_like function, which is not available in version 10g and earlier.

With these techniques, you'll be equipped to manage and manipulate mixed data more efficiently, enhancing your data handling capabilities in SQL.

Feel free to implement either method based on your needs, and remember, experimenting with small datasets can often help clarify which approach works best for your specific situation.