Python extract lines in specific section of SQL script

Question

I have a bunch of stored procedure scripts from sql server that I'd like to extract the custom coding section from. The area I'd like to grab is preceded by this:

/* **************************************************************************************************************************************************** */
    /* * Begin Custom Code ******************************************************************************************************************************** */
    /* **************************************************************************************************************************************************** */

and followed by this:

/* **************************************************************************************************************************************************** */
    /* * End Custom Code ********************************************************************************************************************************** */
    /* **************************************************************************************************************************************************** */

So the script would look like:

/* **************************************************************************************************************************************************** */
    /* * Begin Custom Code ******************************************************************************************************************************** */
    /* **************************************************************************************************************************************************** */

    SELECT col1, col2
    FROM [dbo].[table1]
    ORDER BY col1

    /* **************************************************************************************************************************************************** */
    /* * End Custom Code ********************************************************************************************************************************** */
    /* **************************************************************************************************************************************************** */

Is there a way to read the file and write the lines in between those markers to a separate file as one long string (no tabs or line breaks)?

Are there multiple `Begin Custom Code` blocks in a file or only one? — dstudeba, Jun 26 '15 at 14:39
One approach might be to `split` on both `/*` and `*/` and pick the element(s) of the resulting list that doesn't start with `/*` — LondonRob, Jun 26 '15 at 14:40

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

Assuming you can read the text in yourself, you might do something like this:

(Inspired by this answer)

chunks = sql.replace('/*', '*/').split('*/')
relevant = [x for x in chunks if not x.startswith('/*')

edited May 23 '17 at 12:30

Community

1
1

answered Jun 26 '15 at 14:43

LondonRob

73,083
37
144
201

Oops. My split on multiple conditions doesn't work, I don't think. If someone can fix it, this answer will make sense! – LondonRob Jun 26 '15 at 14:51

dstudeba · Answer 2 · 2015-06-26T15:25:05.693

0

Here is the basic idea

inputArray = inputFile.split('*/')
for val in inputArray:
    if not val.strip().startswith('/*'):
        print(val.strip().split("\n").join(" "))

edited Jun 26 '15 at 15:25

answered Jun 26 '15 at 14:53

dstudeba

8,878
3
32
41

HAHA! Sorry I have been working on PHP recently, I was just fixing it after I realized my mistake. You caught me first! – dstudeba Jun 26 '15 at 14:58

Nizam Mohamed · Answer 3 · 2015-06-26T16:52:51.527

Read each line and do strip on it. If the resulting line is empty or starts with '/*' skip and read next line. Ohterwise append to a list.

sql = '''/* **************************************************************************************************************************************************** */
    /* * Begin Custom Code ******************************************************************************************************************************** */
    /* **************************************************************************************************************************************************** */

    SELECT col1, col2
    FROM [dbo].[table1]
    ORDER BY col1

    /* **************************************************************************************************************************************************** */
    /* * End Custom Code ********************************************************************************************************************************** */
    /* **************************************************************************************************************************************************** */
'''
lines = []
for line in sql.splitlines():
    line = line.strip()
    if not line or line.startswith('/*'):
        continue
    lines.append(line)

' '.join(lines)

'SELECT col1, col2 FROM [dbo].[table1] ORDER BY col1'

Using regex;

' '.join(line.strip() for line in re.sub(r'/\*.*?\*/','',sql,flags=re.DOTALL).strip().splitlines())

'SELECT col1, col2 FROM [dbo].[table1] ORDER BY col1'

Python extract lines in specific section of SQL script

3 Answers3