Oracle SQL - Removing leading patterned substring

Question

I have a data field in an Oracle 12cR1 database. The field is a product descriptor, and the start of the field contains the product ID. It is then followed by the product name. I need to strip off the Product ID. The product ID can be in one of 2 formats... The data typically looks like one of the following:

0P3H - Opus NEM
0P22 - 40021 - Order Capture Self Service

Note that there are some cases where the '-' character is part of the product name, such as

0C34 - HR - Promotions Module

As far as I can tell, I need to delete the following WHEN IT IS AT THE START OF THE STRING...

4 characters then space then dash then space
4 characters then space then dash then space 5 characters space dash space

How would I write Oracle SQL to read a field and REMOVE these 2 different substrings?

Thanks!

docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm — jarlh
– jarlh, Commented Nov 9, 2017 at 13:39

Gordon Linoff · Accepted Answer · 2017-11-10 00:00:32Z

1

From your description, case might be the simplest:

select (case when field like '____ - _____ -%' then substr(field, 15)
             else substr(field, 7)
        end)

edited Nov 10, 2017 at 0:00

answered Nov 9, 2017 at 13:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

user1009073 Over a year ago

This worked, although I had to add the '%' , like this...select (case when field like '____ - _____ -%' then substr(field, 15) else substr(field, 7)

TurtlesAllTheWayDown · Accepted Answer · 2017-11-09 13:48:44Z

0

Substring in reverse to the first '-' and then substr to the end of the string from that starting position.

select TRIM(substr('0P3H - Opus NEM', instr('0P3H - Opus NEM', '-', -1, 1) + 1)) from dual

select TRIM(substr('0P22 - 40021 - Order Capture Self Service', instr('0P22 - 40021 - Order Capture Self Service', '-', -1, 1) + 1)) from dual

answered Nov 9, 2017 at 13:48

TurtlesAllTheWayDown

4062 silver badges11 bronze badges

1 Comment

TurtlesAllTheWayDown Over a year ago

Sorry, I just noticed that you mentioned that in some cases '-' can be part of the name. In that case, the method I described will not work.

Collectives™ on Stack Overflow

Oracle SQL - Removing leading patterned substring

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related