1

replace characters in notepad++ BUT exclude characters inside single quotation marks

Sorry to all users (especially to Avinash Raj) who answered already 1st similiar question - I did simply forget the 2nd kind of string. (And (that is the sad thing) - I'm not able to adjust the solution from 1st similiar question to the 2nd kind of string...)

I have TWO different strings in this kind:

SELECT column_name FROM table_name WHERE column_name IN ('A' , 'st9u' ,'Meyer', ....);
WHERE    a.object_type IN (' 'TABLE'', ''MATEerialIZED VIE3W'   ')

I want replace all characters in notepad++ from upper to lower, BUT exclude from replacement characters inside single quotation marks.

condition: It exists no solid structure before/behind/between the single quotation marks part!

(That means - I can not use the keyword "IN" or signs like "," or "(" or ")" or ";" for this regex ...!)

The once thing is, that two structures for single quotation marks are possible: 'Word|Number' or ''Word|Number'' (but, as shown in 2nd example, with different number of spaces between every single quotation mark!).

target string (the characters inside single quotation marks have to stay unchangend):

select column_name from table_name where column_name in ('A' , 'st9u' ,'Meyer', ....);
where    a.object_type in (' 'TABLE'', ''MATerialIZED VIE3W'   ')

How can I exclude in notepad++ the single quotation marks part (from replacement)?

Community
  • 1
  • 1
ora_job
  • 103
  • 7
  • possible duplicate of [replace characters in notepad++ BUT exclude characters inside single quotation marks](http://stackoverflow.com/questions/32025638/replace-characters-in-notepad-but-exclude-characters-inside-single-quotation-m) – CBroe Aug 30 '15 at 14:02

1 Answers1

1

Could combine a recursive part for getting '... '.... abc '' and use \K for resetting after. This is the part that needs to be skipped. And using kind of the trick, matching remaining words in pipe:

'\s*(?0)?[^']*'\K|(\w+)
  • (?0)? here the pattern is optionally pasted from start
  • \s is a shorthand for whitespace character [ \t\r\n\f]
  • \w is a short for word character [A-Za-z0-9_]

And replace with \L\1 to lower words captured inside the first capture group or \U\1 to upper.
Works for me in NP++ 6.7.9.2 with your sample data. See regex101 for testing the pattern.

enter image description here

Jonny 5
  • 12,171
  • 2
  • 25
  • 42
  • Hi and Sorry for my next mistake - your solution for this case is o.k. but I posted only a part of the whole 2nd statement (stupid)- can you also try to solve this problem? – ora_job Aug 16 '15 at 17:35
  • Hi @ora_job, where does it not work as expected? Just made a little modification! – Jonny 5 Aug 16 '15 at 17:44
  • I post the whole statement in the next comment: (the problem what I didn't mention is - the ''Word/Number'' part is additional included in global single quotation marks - please look at the beginning and the end of the whole 2nd statement...) – ora_job Aug 16 '15 at 17:46
  • SELECT column_name FROM table_name WHERE column_name in ('A' , 'st9u' ,'Meyer', ....); THEN vpl_sql_string := ' SELECT b.mview_name FROM all_objects@'|| pi_2 ||' a ,user_mviews b ,user_mview_detail_relations c WHERE a.object_type IN (''TABLE'', ''MATerialIZED VIE3W'') AND a.owner = UPPER( :bi_1 ) AND a.object_name = c.detailobj_name AND b.mview_name = c.mview_name AND ( b.refresh_method = ''COMPLETE'' OR (b.refresh_method = ''FORCE'' AND b.last_refresh_type = ''COMPLETE'')) AND TO_DATE(a.timestamp,''YYYY-MM-DD:HH24:MI:SS'') > b.last_refresh_date '; – ora_job Aug 16 '15 at 17:48
  • Sorry for this fomat - it was correct formatted, but to much signs for input box - that's why it looks so terrible.... – ora_job Aug 16 '15 at 17:50
  • @ora_job please include your sample in your question as code block. – Jonny 5 Aug 16 '15 at 17:53
  • 1
    Please wait with your solution - I open part3, with correct formatting! – ora_job Aug 16 '15 at 18:01