How can I trim a prefix string in an Oracle SQL query?

Question

I have a column whose values consist of a prefix and then some value, e.g.:

ABC-Car
ABC-Dog
ABC-Suit
CBR-Train

In my query I would like to trim off certain prefixes, e.g. if I trimmed off 'ABC-' it would give me the following:

Car
Dog
Suit
CBR-Train

The only problem is, all characters are valid for the part after the prefix. So it's possible I could have:

ABC-ABC-Cat

I want this trimmed value to return:

ABC-Cat

The TRIM and LTRIM functions appear to use pattern/character matching rather than once-only string matching. Is there a way to accomplish what I want with either of these functions or with another built-in function?

Currently I'm thinking I'll have to use a CASE statement to check for that prefix at the beginning of each value and if it exists, SUBSTR it off, else return the column unchanged. Hoping there is a simpler way.

David Oneill · Accepted Answer · 2010-02-12 19:49:23Z

6

regexp_replace is what you'll want to use for this.

Usage examples:

select regexp_replace('ABC-Car', 'ABC-(.*)','\1') from dual; --returns 'Car'
select regexp_replace('ABC-ABC-Car', 'ABC-(.*)','\1') from dual; --returns 'ABC-Car'
select regexp_replace('ABC-ABC-Car', 'ABC-(.*)','\1') from dual; --returns 'CBR-Train'

answered Feb 12, 2010 at 19:49

David Oneill

13.2k19 gold badges63 silver badges70 bronze badges

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

2 Comments

OMG Ponies Over a year ago

Be aware that regex functions are 10g+.

David Oneill Over a year ago

@Ponies, it sometimes feels like you're following me around: you make it to all the SQL questions. :) +1

Collectives™ on Stack Overflow

How can I trim a prefix string in an Oracle SQL query?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related