Using Substring and INSTR in Oracle

Question

I am not able to create a this Select query :

When I query:

SELECT DISTINCT PRODUCT_ID FROM SYSADM.TABLE;

I get the output as:

EMA12345

EMA4567844

EMA4545455

US12342

NA4545454

PRS767657

Now I want to fetch the data such as product id retrieved is:

That means every product id other than starting with EMA should come as it is and those starting with EMA should be without EMA.

Need to append this within another select query. Can't use procedure.

Sathyajith Bhat · Accepted Answer · 2012-01-20 05:20:26Z

1

Use the REPLACE function:

SELECT DISTINCT REPLACE(PRODUCT_ID, 'EMA') FROM SYSADM.TABLE

edited Jan 20, 2012 at 5:20

Sathyajith Bhat

22k22 gold badges102 silver badges141 bronze badges

answered Jan 20, 2012 at 5:19

schtever

3,25019 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eaolson · Accepted Answer · 2012-01-20 05:19:50Z

1

SELECT DECODE( INSTR( product_id, 'EMA' ), 1, SUBSTR( product_id, 4 ), 
               product_id ) AS my_column
  FROM SYSADM.TABLE

Basically, if the first three characters of product_id are "EMA" then use the 4th character to the end of product_id, otherwise, use the whole product_id.

answered Jan 20, 2012 at 5:19

eaolson

15.2k7 gold badges46 silver badges62 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:19:08Z

1

You could chain togethor replace statements such as:

select replace( replace( replace( product_id, 'EMA' ), 'US' ), 'NA' ) from sysadm.table;

Or follow one of the alternatives from here.

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Jan 20, 2012 at 5:22

josh-fuggle

3,1672 gold badges29 silver badges27 bronze badges

1 Comment

josh-fuggle Over a year ago

Oops sorry, misread. Thought you wanted to remove all prefixes.

Collectives™ on Stack Overflow

Using Substring and INSTR in Oracle

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related