ORACLE SQL: how to get text before a specific word

Question

Using Oracle,

1. how can i get the text before a specific word(CITY) including the word CITY

Sample:

22nd street westlake 1378 california city 
32nd street texas 1111 houston city

Result:

California city 
Houston city

2. Removing the city from item#1.

Sample:

22nd street westlake 1378 california city 
32nd street texas 1111 houston city

Result:

22nd street westlake 1378
32nd street texas 1111

updated the question. thanks

Answer 1

It seems like you need to remove string or numbers from your string.

Try to use regexp_replace as follows:

1.

regexp_replace( '1378 california city 1111 houston city' , '[^0-9 ]')

regexp_replace( '1378 california city 1111 houston city' , '[0-9]')

Check the complete example here: Db<>fiddle