Regex to generate dynamic sql

Question

I want to generate dynamic sql on Notepad++ based on some rules. These rules include everything, so no sql knowledge is needed, and are the following:

1. Dynamic sql must have each single quote escaped by another single quote ( 'hello' becomes ''hello'')
2. Each line should begin with "+@lin"
3. If a line has only whitespace, nothing should be following the "+@lin", despite following rules
4. Replace each \t directly following "+@lin" with "+@tab"
5. Add " +' " after the @lin/@tab sequence
6. Add a single quote at the end of line

So, as an example, this input:

select 1,'hello'
from        --two tabs exist after from

    table1

should become:

+@lin+'select 1,''hello'''
+@lin+'from     --two tabs exist after from'
+@lin
+@lin+@tab+'table1'

What I have for now is the following 4 steps:

• Replace single quote with double quotes to cover rule 1
• Replace ^(\t*)(.*)$ with \+@lin\1\+'\2' to cover rules 2,5,6
• Replace \t with \+@tab to cover rule 4
• Replace (\+@tab)*\+''$ with nothing to cover rule 3

Notice that this mostly works, except for the third replacement, which replaces all tabs, and not only the ones at the beginning. I tried (?<=^\t*)\t with no success- it matches nothing.

I'm looking for a solution which satisfies the rules in as few replacement steps as possible.

Answer 1

After replacing single quotes with 2 quotes, you can do the rest in a single step:

Not very elegant for processing multiple TABs, but it works.

• Ctrl+H
• Find what: ^(?:(\t)(\t)?(\t)?(\t)?(\t)?(\S.*)|\h*|(.+))$
• Replace with: +@lin(?1+@tab+(?2@tab+)(?3@tab+)(?4@tab+)(?5@tab+)'$6')(?7+'$7')
• CHECK Match case
• CHECK Wrap around
• CHECK Regular expression
• UNCHECK . matches newline
• Replace all

Explanation:

^       # beginning of line
    (?:     # non capture group
        (\t)    # group 1, tabulation
        (\t)?   # group 2, tabulation, optional
        (\t)?   # group 3, tabulation, optional
        (\t)?   # group 4, tabulation, optional
        (\t)?   # group 5, tabulation, optional
        (\S.*)  # group 6, a non-space character followed by 0 or more any character but newline
      |       # OR
        \h*     # 0 or more horizontal spaces
      |       # OR
        (.+)    # group 7, 1 or more any character but newline
    )       # end group
$       # end of line

Replacement:

+@lin       # literally
(?1         # if group 1 exists
    +@tab+      # add this
    (?2@tab+)   # if group 2 exists, add a second @tab+
    (?3@tab+)   #   id
    (?4@tab+)   #   id
    (?5@tab+)   #   id
    '$6'        # content of group 6 with single quotes
)           # endif
(?7         # if group 7 exists
    +           # plus sign
    '$7'        # content of group 3 with single quotes
)           # endif

Screenshot (before):

Screenshot (after):