I have a string and I would like to capture everything before " where " but the one that is NOT in parentheses:
string:'update sa.table_bpm_proc_inst set assignee=(select parameter_8 from mqm.fc_launcher_parameterization where list_name = tables_permitidas_free_statement and flag = 1 and parameter_2 = sa.table_bpm_proc_inst) ,focus_table_num =(select 65 from dual where 1=1),focus_objid= 2 where objid =-4t30000(d-je1000)abcde~0006461
The only solution I could think of uses positive lookahead, this works when I test it on https://regex101.com/
(.)(?=where(?![^(])))
But now I need to use it in an Oracle SQL statement:
select REGEXP_SUBSTR ('update sa.table_bpm_proc_inst set assignee=(select parametro_8 from mqm.fc_launcher_parametrizacion where list_name = mesas_permitidas_sentencia_libre and flag = 1 and parametro_2 = sa.table_bpm_proc_inst) ,focus_table_num =(select 65 from dual where 1=1),focus_objid= 2 WHERE objid =-4t30000(d-je1000)abcde~0006461
‘,’(.)(?=where(?![^(])))’,1,1,‘i’) from dual
and it doesn’t work since (I think) look ahead is not supported. Can anyone help with an alternative expression that works in pl/sql?
What about using substr like this?
REGEXP_SUBSTR( 'An example sentence.' , 'where+' , 1, 1) AS result
I mean, wouldnt that just be where objid.* ?
Oracle uses a POSIX regexp, which doesnt include lookahead/behind.
That said, you could pull the whole string back to PHP, and use its regexp substringing, which is PCRE and does include lookahead searching, and then put that into your oracle statement, rather than trying to use Oracle’s engine to do the regex’ing.
Google Translate, away!
Oracle no admite Lookahead.
Si su Regex requiere mirar hacia adelante, lo que parece que sí, deberá hacer Regex en PHP.
Sorry for the language, I didn’t get it.
all I need is this regular expression
(.)(?=where(?![^(])))))
but for oracle, since I am not able to get it.
Could someone help me?
We need a bit more information about your string.
Is it in PHP already? What does your PHP code look like?
I understand that PHP refers to this
You are posting this in a PHP thread, so my assumption is you’re using PHP to make the query against the database.
Where are you putting your query such that it “doesn’t work”?
In PL/SQL developer with the command regexp_subst
select REGEXP_SUBSTR (‘update sa.table_ set assi=(select parametro_8 from tabla where nombre_ = tablas_permitidas_sentencia_libre and flag = 1 and parametro_2 = fdpm_p) ,focus__num =(select 65 from dual),objid= 2 where objid =-4je1000)abcde~0006461
‘,’(.)(?=where(?![^(])))’,1,1,‘i’) from dual;
Well if you’re just manually running the command… manually do the substring yourself?
