0

Suppose we want to keep the entire line of a string only if a particular word say e.g 'test' appears at starting of line.

If it appears anywhere then the entire line should be removed

e.g

if function_test()=5; //here this entire line should be removed

test sample =5; //here this entire should be there
CDspace
  • 2,639
  • 18
  • 30
  • 36
BeastLesnar
  • 71
  • 1
  • 3
  • 7
  • There is no stored procedure as such.From whole source code I have to do the above stuff i.e remove that particular line where word test is coming anywhere apart from beginning – BeastLesnar Apr 03 '17 at 14:27
  • This is more of regex kind of thing – BeastLesnar Apr 03 '17 at 14:27
  • If there is no stored procedure then why did you tag it with PL/SQL? –  Apr 03 '17 at 14:29
  • Are these multiple rows in a table, or multiple lines in a varchar2/CLOB value or variable, or something else? i..e what are you removing the lines *from*? And what about lines that don't contain 'test' at all? – Alex Poole Apr 03 '17 at 15:12
  • @Alex the lines that don't contain 'test' should be there.These are multiple lines of a source code of clob data type.These source code passes through a pl/sql block after passing the lines having test anywhere apart from beginning should be removed – BeastLesnar Apr 03 '17 at 15:21
  • Do the matching lines have to be completely removed (so line count of CLOB goes down), or can they just be replaced with empty lines? – Alex Poole Apr 03 '17 at 16:37
  • @Alex They have to be removed completely.Yes the line count of CLOB goes down – BeastLesnar Apr 03 '17 at 16:48
  • Then I think you'll have to process the CLOB line by line and rebuild it with the lines that you want to keep, as Tamas suggested. Doing `regexp_replace(old_str, '^.+test.*$', null, 1, 0, 'm')` will blank them, but I don't know of a way to remove the line completely (in multiline mode, in Oracle). – Alex Poole Apr 03 '17 at 16:50
  • @alex Thanks for your help – BeastLesnar Apr 03 '17 at 17:01

3 Answers3

0

From Oracle 10g R2 on you should be able to use the anchor \A to require the match at the beginning of the string (will only work for single-line strings thus).

http://www.regular-expressions.info/oracle.html

Lucero
  • 59,176
  • 9
  • 122
  • 152
  • There are multiple lines of a source code of clob data type.These source code passes through a pl/sql block after passing the lines having the word 'test' anywhere apart from beginning should be removed.So input is source code and I have to write something in the pl/sql block so that this happens – BeastLesnar Apr 03 '17 at 16:58
0

What do you mean by keep / remove lines? Where is this regex supposed to run? I.e. is it a part of an SQL command, or part of a grep, or sg else?

Regarding SQL you can use LIKE operator:

WHERE line LIKE 'test%'

You can use substring too:

WHERE substring(line, 1, 4) = 'test'

Using grep or any other language, you can specify start of line, e.g.:

grep '^test' bigfile.txt
Tamas Rev
  • 7,008
  • 5
  • 32
  • 49
  • this regex will be written in pl/sql block which passes the whole source coden as input string and in that remove that particular line where word test is coming anywhere apart from beginning – BeastLesnar Apr 03 '17 at 14:50
  • @BeastLesnar then, the `LIKE` operator should do the job for you. For case insensitive match you can use the `LOWER` function too: `LOWER(line) LIKE 'test%'`. – Tamas Rev Apr 03 '17 at 14:53
  • I did not get you completely.Could you please elaborate as I am new to this – BeastLesnar Apr 03 '17 at 14:55
  • @BeastLesnar please show the code you have - it can be incomplete, no problem. Then I'll add this `LIKE` expression, that should clear up everything :) – Tamas Rev Apr 03 '17 at 15:07
  • There are multiple lines of a source code of clob data type.These source code passes through a pl/sql block after passing the lines having the word 'test' anywhere apart from beginning should be removed.So input is source code and I have to write something in the pl/sql block so that this happens – BeastLesnar Apr 03 '17 at 15:31
  • Then, first you need to read that text line by line. As explained at [this](http://stackoverflow.com/questions/11647041/reading-clob-line-by-line-with-pl-sql) SO question. – Tamas Rev Apr 03 '17 at 15:36
  • Can't there be solution like this -: REGEXP_REPLACE (source_code,'^test.*', '',1,0,'m'); Here it searches for test in beginning if it finds then removes that line.I want exact opposite if start then keep otherwise remove – BeastLesnar Apr 03 '17 at 15:41
0

Try...

...
WHEN REGEXP_LIKE(string,'^test','i') THEN
     //this is a good line, do what you want or return string;
END
...
unleashed
  • 771
  • 3
  • 9