-1

I've this expresion

(CONJ|ADL|ALD|ALM|ATR|VRD)(.)*{1,6}(TERPLN|SUITE|AGP|ALM|PT|$)

To extract from

CL 18 A SUR 29 C 80 VRD LOS ARBOLES PT 20

I need extract 'VRD LOS ARBOLES PT' but with REGEXP_SUBSTR return 'VRD LOS ARBOLES PT 20 '

Please help me with this

REGEXP_SUBSTR('CL 18 A SUR 29 C 80 VRD LOS ARBOLES PT 20','(CONJ|ADL|ALD|ALM|ATR|VRD)(.)*{1,6}(TERPLN|SUITE|AGP|ALM|PT|$)' )
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
Milton
  • 37
  • 4
  • is there a logic for this extraction? – Vamsi Prabhala Dec 06 '18 at 18:37
  • What does this do `(.)*{1,6}`? Is it really valid in oracledb? as `*` itself is a quantifier and you can't quantify a quantifier. – Pushpesh Kumar Rajwanshi Dec 06 '18 at 18:47
  • `(.*){1,6}` makes no sense - "select all characters, repeat between 1 and 6 times" is the same as `(.*)`. The problem is that you are reading all the way to the end of the string with (.*), and that is allowed by `$` in the alternation at the end. Use `(.*?)` instead of `(.*){1,6}` (and then read about non-greedy matching to understand `(.*?)` if you aren't already familiar with it). –  Dec 06 '18 at 18:49
  • @WiktorStribiżew - the OP's only example already shows more than six characters need to be selected, so that can't be the right solution. –  Dec 06 '18 at 18:50

1 Answers1

0

You are probably looking for something like this:

with
  inputs as (
    select 'CL 18 A SUR 29 C 80 VRD LOS ARBOLES PT 20' address from dual
  )
select regexp_substr(address, 
       '(CONJ|ADL|ALD|ALM|ATR|VRD).*?(TERPLN|SUITE|AGP|ALM|PT|$)') token
from   inputs
;

TOKEN
---------------------
VRD LOS ARBOLES PT

The key is what's between the alternations - .*? in this case.

However, be warned that this is a dangerous approach - what if the street name (or whatever that is) actually had the substring PT in it?

VRD COMPTON VALLEY

the search will return VRD COMPT

and the issue is with your data model, not the specific query you use on such data. Perhaps you can require that there is a space before the final alternation; but even then, you will get wrong answers in an input like

VRD GARDEN OF PTOLEMY

  • I see someone downvoted... An explanation would be helpful: what's wrong with this answer? –  Dec 06 '18 at 19:01