Using nested SQL string functions to find ERP customer values in a table

SQL expert Rudy Limeback explains the value of nested SQL string functions when extracting the ERP customer value for all rows in a table.

I have a column in one of my databases that carries data such as the example below:

TME_DTE="20080111"; PRD_SLBL="24310801"; OWS_CCT_LIN=" SALES"; OWS_CUS_GRP="D"; OWS_SLS_ORG="BOP"; GGY_ACG_DPO="BPA"; TPT_SHP_CND="1C"; OWS_ERP_CUS="154997"; UOM="L20"; PRD_ACL_DEN="1"; QTY="21807.104"; TIMEZONE="CST"; DOCNO=;

I would like to extract just the number of the ERP customer from the column above. If I get a row as an example and use the INSTR function, I am able to find the position where I want to begin the extract of the ERP customer: 149. Also, using SUBSTR('string',149,6) I am able to get the ERP customer number that I am looking for. The question is: How do I apply SUBSTR function to find the ERP customer value for all rows on my table?

The answer involves using nested SQL string functions. Nest the INSTR function inside the SUBSTR function..

SELECT SUBSTR(datacolumn
           , INSTR(datacolumn,'OWS_ERP_CUS="',1,1)+13
           , 6)
         AS ERP_cust
  FROM ...

The INSTR function shown will return the first position where 'OWS_ERP_CUS="' occurs. To this position, you must add 13, to move forward past that string to the first character of the customer number. Then take six characters with SUBSTR and Bob's your uncle.

Dig Deeper on Oracle development languages