Parsing SQL Script with Regular Expression

Hi there,

Feel free to point me to the correct newsgroup if you can thik of a better one.


I would like to parse a SQL script using Regular Expressions

For example, I have this string in My SQL Script:

CREATE      VIEW      vw_Something     AS
...
..
..
..

GO


I would like to grab the view name and view contents.

I've come up with this Regular expression string:

CREATE[\s]*VIEW[\s]*([\S]*)[\s]*AS


This will give me the view name in the first bracketed subselect.

Getting the view contents seems to be more difficult. I want to find all the text between these patterns:

FROM: the end of the last regular expression
TO a whitespace surround 'GO' string, so something like this:

([^[\s]GO[\s]])[\s]GO[\s]


The problem I have is a I cant 'not' match an entire string.

Can anybody help? I can reexplain if required!!!
LVL 30
nmcdermaidAsked:
Who is Participating?
 
Shiju SasidharanAssoc Project ManagerCommented:

try this
'-----------------------------------------------------------------

CREATE[\s]*VIEW[\s]*([\S]*)[\s]*AS([\s\S]*?)GO

'-----------------------------------------------------------------

second bracketed subselect will give u the content

it is better to use [ \t]  instead of [\s] if u need to match only tab and space

[\s\S]*?     will get all contents including new lines until "GO"
(this wont be greedy match and will stop searching on finding first "GO" )

hope this willl help u

;-)
Shiju




0
 
Shiju SasidharanAssoc Project ManagerCommented:
what are the starting and ending pattern ?
can u give a sample data and explain what all data u want to extract from that ?
;-)
Shiju
0
 
nmcdermaidAuthor Commented:
Fantastic Shiju, I had a feeling that the non greedy match was what I wanted but I couldn't work out the syntax!!

Thats great, its solved my problem.
0
 
Shiju SasidharanAssoc Project ManagerCommented:
hi nmcdermaid
i am happy bcoz u r happy
thank u for the points
;-)
Shiju
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.