• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

searching using regular expressions

Hi

I want to be able to use a regular expression which goes through a large txt file where all my Packages (around 100) are kept.
The objective is for the ruby script/regex to return the Package name and the tables and joins used.

CREATE OR REPLACE PACKAGE TEST
SELECT *
FROM
TABLE1
INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
LEFT JOIN  TABLE3 ON TABLE1.REF = TABLE3.REF
WHERE TABLE1.ID > 14


CREATE OR REPLACE PACKAGE TEST2
SELECT *
FROM
TABLE1a
INNER JOIN TABLE2a ON TABLE1a.ID = TABLE2a.ID
LEFT JOIN  TABLE3a ON TABLE1a.REF = TABLE3a.REF
LEFT JOIN  TABLE4  ON TABLE4.NAME = TABLE2a.NAME
WHERE TABLE4.NAME IN ('FRED','BRIAN')

etc etc


ouptut file would read as .......

CREATE OR REPLACE PACKAGE TEST
TABLE1
INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
LEFT JOIN  TABLE3 ON TABLE1.REF = TABLE3.REF

CREATE OR REPLACE PACKAGE TEST2
INNER JOIN TABLE2a ON TABLE1a.ID = TABLE2a.ID
LEFT JOIN  TABLE3a ON TABLE1a.REF = TABLE3a.REF
LEFT JOIN  TABLE4  ON TABLE4.NAME = TABLE2a.NAME

any help much appreciated.

Regards
0
PHIL Sawyer
Asked:
PHIL Sawyer
  • 3
  • 3
1 Solution
 
ozoCommented:
Why does the output contain
TABLE1
but not
TABLE1a
?
0
 
PHIL SawyerAuthor Commented:
Sorry - my fault - it should.

Regards
0
 
ozoCommented:
while line = STDIN.gets
     unless line=~/SELECT|FROM|WHERE/
       puts line
     end
  end
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
PHIL SawyerAuthor Commented:
Thanks for that - one further question if I may....
I want to be able to include the select * from line only if a word follows the word "from" otherwise I do not want to include the line eg
select * from mytable - this is ok
select *
from
mytable where  etc - then I'm not interested in returning the lines select * & from
Regards
0
 
ozoCommented:
while line = STDIN.gets
     unless line=~/^\s*(WHERE.*)?(SELECT\s*\*\s*)?(FROM\s*)?$/i
       puts line
     end
  end
0
 
PHIL SawyerAuthor Commented:
Thanks
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now