Home » SQL & PL/SQL » SQL & PL/SQL » Help with REGEXP (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 )
| Help with REGEXP [message #631498] |
Wed, 14 January 2015 04:37  |
|
|
In a script file I have some commented out line as below
select col1,col2, /*some text 1 */col3,/*some text 2*/col4
from mytable
Now I want to remove this commented out lines by using REGEXP function. Please help me.
Based on these scripts I create view dynamically in each month.
Thanks in advance.
|
|
|
|
|
|
|
|
| Re: Help with REGEXP [message #631506 is a reply to message #631501] |
Wed, 14 January 2015 05:02  |
 |
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> with
2 data as (
3 select 'select col1,col2, /*some text 1 */col3,/*some text 2*/col4
4 from mytable' v
5 from dual
6 )
7 select regexp_replace(v, '/\*[^(*/)]*\*/') v
8 from data
9 /
V
----------------------------------------
select col1,col2, col3,col4
from mytable
|
|
|
|
| Re: Help with REGEXP [message #631529 is a reply to message #631506] |
Wed, 14 January 2015 12:19 |
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You can't group inside brackets, so [^(*/)] means not (, not *, not / not ):
SQL> with
2 data as (
3 select 'select col1,col2, /*some (text) 1 */col3,/*some text 2*/col4
4 from mytable' v
5 from dual
6 )
7 select regexp_replace(v, '/\*[^(*/)]*\*/') v
8 from data
9 /
V
----------------------------------------------------------
select col1,col2, /*some (text) 1 */col3,col4
from mytable
All we need is non-gready .*:
with
data as (
select 'select col1,col2, /*some (text) 1 */col3,/*some text 2*/col4
from mytable' v
from dual
)
select regexp_replace(v, '/\*.*?\*/') v
from data
/
V
----------------------------------------
select col1,col2, col3,col4
from mytable
SQL>
SY.
|
|
|
|
|
|
| Re: Help with REGEXP [message #631540 is a reply to message #631506] |
Wed, 14 January 2015 22:05 |
|
|
Thanks All
But if there is line break within comments. It is not working. Could it be resolved?
Below SQL which has line break within '/* some text */' is not working.
"select col1,col2, /*some
(text) 1 */col3,col4
from mytable"
Please help.
|
|
|
|
|
|
| Re: Help with REGEXP [message #631546 is a reply to message #631542] |
Thu, 15 January 2015 01:46 |
|
|
Yes I tried.
If there is line break it was not working.
To resolve this first I have replaced the CHR(10) with the text ' linebreak ', then applied your logic with REGEXP_REPLACE function and then again replaced the ' linebreak ' with CHR(10).
Below is my PL/SQL block.
Declare
CURSOR cur_clob is
select pkvr_period, var_part
from pkv_ratio_calc_vw_definition
where pkvr_period > '01-aug-2015';
l_var_part CLOB;
begin
for i in cur_clob loop
l_var_part := replace(i.var_part,chr(10),' linebreak ');
while instr(l_var_part,'/*') <> 0 or instr(l_var_part,'*/') <>0 loop
l_var_part :=regexp_replace(l_var_part,'/\*.*?\*/');
end loop;
update pkv_ratio_calc_vw_definition
set var_part = replace(l_var_part,' linebreak ',chr(10))
where pkvr_period = i.pkvr_period;
commit;
end loop;
end;
|
|
|
|
|
|
|
|
| Re: Help with REGEXP [message #631557 is a reply to message #631548] |
Thu, 15 January 2015 05:05 |
|
|
Now I have resolved it as below:
Declare
CURSOR cur_clob is
select pkvr_period, var_part
from pkv_ratio_calc_vw_definition
where pkvr_period = '01-sep-2015';
l_var_part CLOB;
begin
for i in cur_clob loop
dbms_output.put_line('test');
l_var_part := i.var_part;
while instr(l_var_part,'/*') <> 0 or instr(l_var_part,'*/') <>0 loop
l_var_part :=regexp_replace(regexp_replace(regexp_replace(l_var_part,chr(10),' linebreak '),'/\*.*?\*/'),' linebreak ',chr(10));
end loop;
dbms_output.put_line(substr(l_var_part,1,32000));
end loop;
End;
Is there any better way? Here REGEXP is nested thrice. I want to improve it.
|
|
|
|
|
|
| Re: Help with REGEXP [message #631576 is a reply to message #631557] |
Thu, 15 January 2015 08:54 |
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
mokarem wrote on Thu, 15 January 2015 06:05Is there any better way? Here REGEXP is nested thrice. I want to improve it.
Yes there is - scrap your code. Use my solution. Add proper match_param value. Read about match_param in REGEXP_COUNT.
SY.
|
|
|
|
|
|
| Re: Help with REGEXP [message #631604 is a reply to message #631582] |
Thu, 15 January 2015 21:36 |
|
|
If I use the match_param, it gives the result null:
with
data as (
select 'select col1,col2, /*some
(text) 1 */col3,/*some text 2*/col4
from mytable' v
from dual
)
select regexp_replace(v,'/\*.*?\*/','','','','m') v
from data
/
|
|
|
|
|
|
|
|
| Re: Help with REGEXP [message #631611 is a reply to message #631607] |
Fri, 16 January 2015 05:13 |
|
|
I got the below syntax:
REGEXP_REPLACE
(sourcestr, pattern [,replacestr [, position [, occurrence [, options]]]])
For my SQL, 'Position' should from the beginning of source text. 'Occurrence' means number of matching pattern. For my SQL it is not fixed.
Notice in below SQL I have given 'n' for 'Options' parameter which gives me the expected result.
For 'Occurrence' param I have give 0 from my assumption. Is 0 means unlimited for this param?
with
data as (
select 'select col1,col2, /*some
(text) 1 */col3,/*some text 2*/col4, /*some
text*/col5, /*some % text*/col6
from mytable' v
from dual
)
select regexp_replace(v,'/\*.*?\*/','',1,0,'n') v
from data
/
v
---------------------
select col1,col2, col3,col4, col5, col6
from mytable
If I give 'm' for option parameters:
with
data as (
select 'select col1,col2, /*some
(text) 1 */col3,/*some text 2*/col4, /*some
text*/col5, /*some % text*/col6
from mytable' v
from dual
)
select regexp_replace(v,'/\*.*?\*/','',1,0,'m') v
from data
/
V
-----------------------
select col1,col2, /*some
(text) 1 */col3,col4, /*some
text*/col5, col6
from mytable
Now I am confused with the options parameter 'm' and 'n'.
In your links description about 'm' is "treats the source string as multiple lines".
|
|
|
|
| Re: Help with REGEXP [message #631616 is a reply to message #631611] |
Fri, 16 January 2015 08:37 |
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Confused about what? You are trying to look for comments (/* followed by any number of any characters followed by */). By default dot doesn't match new line character, so pattern '/\*.*?\*/' will not match comment that spans multiple lines. Now you use match_param m which tells Oracle to apply regexp to each line, so how would it help finding /* xxx yyy */ if line is /* xxx and next line is yyy */ ? You WANT regexp to continue search in next line while searching for a pattern while m will force regexp to stop at the end of the line and start over, so m is out of the question. Issue we have, as I already noted, is dot doesn't match new line character. That's why we need match_param n. Then regexp will find /* then will start looking for a matching */ and will not stop at new line character. Hope it is clear now.
SY.
[Updated on: Fri, 16 January 2015 08:41] Report message to a moderator |
|
|
|
|
|
| Re: Help with REGEXP [message #631848 is a reply to message #631627] |
Tue, 20 January 2015 21:23 |
|
|
Many Thanks for your detail explanation about option parameter's value 'm' and 'n'.
Now if I ommit '?' from the pattern:
with
data as (
select 'select col1,col2, /*some
(text) 1 */col3,/*some text 2*/col4, /*some
text*/col5, /*some % text*/col6
from mytable' v
from dual
)
select regexp_replace(v,'/\*.*\*/','',1,0,'n') v
from data
V
=========================
select col1,col2, col6
from mytable
Would you please explain the purpose of '?' as you have explained the 'm'/'n' in detail?
I have gone through some links and found that '?' is a quantifier character and 'Match 0 or 1 time'. This is not clear to me.
|
|
|
|
|
|
|
|
| Re: Help with REGEXP [message #631970 is a reply to message #631961] |
Thu, 22 January 2015 08:10 |
|
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:Non greedy means "it will not stop with first reported match and display the first match but continues to find maximum combinations in the given string"
No this is greedy, non-greedy means it stops at the first occurrence; normal behaviour is to go until the last one to find the largest string that matches the pattern.
Quote:Please give me an appropriate example of greediness to understand it more clearly
You give it in your last example: Oracle searches from /* until it find the last */.
With "?", in the previous example, it searches from /* until it find one */, and does it several times as this happens several times in your string.
Another example:
SQL> with data as (select 'Antonio Mohamed Abdulha Khan' v from dual)
2 select v, regexp_substr(v,'A.* ') greedy, regexp_substr(v,'A.*? ') non_greedy
3 from data
4 /
V GREEDY NON_GREEDY
---------------------------- ---------------------------- ----------------------------
Antonio Mohamed Abdulha Khan Antonio Mohamed Abdulha Antonio
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Aug 26 12:09:53 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
