Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.

This was last published in September 2008

Dig Deeper on Oracle development languages

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close