Extract the Last 10 Digits from Mobile Numbers Using SQL

Learn how to efficiently extract the last 10 digits from mobile numbers in Oracle SQL with simple techniques.
---

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: Need Regex pattern for right side of 10 digits from mobile Number

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Extracting the Last 10 Digits from Mobile Numbers in Oracle SQL

In the realm of data management, particularly with telecommunications data, extracting specific portions of text can often be a necessary yet complex task. A common requirement may arise where you need to isolate the last 10 digits from a mobile number in a database. This guide will guide you through this process using Oracle SQL's effective string manipulation features.

Understanding the Problem

You may encounter cases where mobile numbers are stored with varying lengths, but for your application, you only need the final 10 digits. For example:

Input: 919345678901

Output: 9345678901

Similarly:

Input: 09934567892

Output: 9934567892

What you need is a straightforward method to perform this extraction without delving deep into complex regular expressions.

The Simple Solution: Using the SUBSTR Function

In Oracle SQL, rather than using regular expressions which can be slow and complex, you can leverage the fast and efficient SUBSTR function. The SUBSTR function allows you to retrieve a substring from a string, making it ideal for our needs.

Sample SQL Setup

Here’s how you can set up your sample data in a table named test:

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

This structure allows you to simulate a small dataset for testing your queries.

SQL Query to Extract Last 10 Digits

Now, let's see how you can write the SQL query to extract the desired output:

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

Explanation of the Query

SELECT col: This selects the original mobile number from the table.

SUBSTR(col, -10): This is the critical part of the query where you call the SUBSTR function. The -10 parameter tells Oracle to start extracting characters from the 10th position from the right end of the string.

AS result: This provides a name for the output column, making the results clearer.

Results of the Query

When this query is executed, you will see output similar to the following:

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

Conclusion

Using the SUBSTR function in Oracle SQL is a straightforward and efficient way to extract the last 10 digits from mobile numbers. By avoiding regular expressions, you benefit from faster execution time and improved performance, especially with large datasets.

This method not only simplifies your queries but also enhances maintainability in your SQL scripts. The next time you need to manipulate mobile number data, remember the power of SUBSTR!