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
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading