CODEWITHSUNDEEP
sqloracle-database
aw crud
aw crud

Reputation: 8891

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

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.

Upvotes: 4

Views: 6855

Answers (1)

David Oneill
David Oneill

Reputation: 13065

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'

Upvotes: 6

Related Questions