Transforming Oracle DB Data Using SUBSTR and REGEXP Functions

Learn how to convert Oracle DB column data from one format to another using `SUBSTR` and `REGEXP` in this easy-to-follow guide.
---

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: SUBSTR to ADD value in oracle

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Transforming Oracle DB Data Using SUBSTR and REGEXP Functions

When working with Oracle databases, you may frequently encounter data that needs formatting or cleaning to meet specific requirements. A common issue arises when you want to transform character data that contains delimiters. In this guide, we will address how to convert a specific format of data in Oracle DB using the powerful SUBSTR and REGEXP functions.

The Challenge

Suppose you have a table with a column structured like this:

COL 1abc,mno:EMPxyz:EMP;tyu,opr:PROFabc,mno:EMP;tyu,opr:PROFIn this format, the requirement is to adjust the data so that all the entries after a colon (:) and before a semicolon (;) are represented appropriately, substituting commas with a semicolon to create a cleaner format.

The target output should look like this:

COL 1abc:EMP;mno:EMPxyz:EMP;tyu:PROF;opr:PROFabc:EMP;mno:EMP;tyu:PROF;opr:PROFTo accomplish this, we need to break down the format transformation into manageable steps using SQL queries.

Solution Breakdown

Here’s how to approach the transformation using Oracle SQL:

Step 1: Create Sample Data

First, we create a common table expression (CTE) to simulate the scenario with sample data:

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

Step 2: Split the Data into Rows

Next, we can utilize the REGEXP_SUBSTR function to separate the values by the semicolon delimiter. Here’s the involved SQL:

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

Step 3: Transform the Output

Finally, we’ll replace any commas with the appropriate format using the SUBSTR and INSTR functions before aggregating back the results. Here’s the complete query for the transformation:

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

Result

When you execute the full SQL statement, you should obtain the desired output:

IDNEW_VAL1abc:EMP;mno:EMP2xyz:EMP;tyu:PROF;opr:PROF3abc:EMP;mno:EMP;tyu:PROF;opr:PROFThis final result showcases that the original data has been formatted as required, proving that SUBSTR and REGEXP functions can be immensely powerful for data manipulations in Oracle.

Conclusion

In this guide, we explored a practical approach to transforming Oracle DB column data using the SUBSTR, REGEXP, and LISTAGG SQL functions. This methodology not only cleans up the format but also enhances readability and usability in your database applications.

Feel free to adapt the provided queries to your specific needs, and happy querying!