How to remove the last 3 words from a string in PL/SQL?

Question

I have strings like these:

Jack & Bauer Limited Company Bristol

Streetfood Limited Company München

Brouse with High Jack UnlimiteD Company London

What I want to have is just the company names like:

Jack & Bauer

Streetfood

Brouse with High Jack

So in every case, I have to delete the last 3 words, because the names can be consist a lot of words. I know I have to use regexp, but I dont know how.

`REGEXP_REPLACE('Brouse with High Jack UnlimiteD Company London', '\w+(\s+\w+){2}$', '')` — Dmitry Bychenko, Oct 02 '18 at 11:16
You have two good answers below that work for you *if* every single row in your dataset has three extra words at the end. Is that really the case? Or do some rows have fewer 'extra' words? — Christian Palmer, Oct 02 '18 at 11:35
The proposed answers don't use PL/SQL though. Is that a requirement? — William Robertson, Oct 02 '18 at 13:48

score 2 · Answer 1 · answered Oct 02 '18 at 11:27

2

While you can use regular expressions to do this you don't have to. This task can be accomplished using a combination of INSTR and SUBSTR:

SELECT SUBSTR(FIELD1, 1, INSTR(FIELD1, ' ', -1, 3)-1) AS NAME
  FROM TABLE1

SQLFiddle here

Best of luck.

answered Oct 02 '18 at 11:27

Bob Jarvis - Слава Україні

48,992
9
77
110

Gordon Linoff · Accepted Answer · 2018-10-02T11:55:56.463

1

Here is one method:

select regexp_replace(str, '( [^ ]+){3}$', '')

Here is a rextester.

edited Oct 02 '18 at 11:55

answered Oct 02 '18 at 11:16

Gordon Linoff

1,242,037
58
646
786

How to remove the last 3 words from a string in PL/SQL?

2 Answers2