trying to get text between certain words
The text below looks messy but thats how we get files sent to us sometimes.
I want to retrieve all text between "JOIN" and "="
sample text....
SELECT
*
FROM mytable ppst
LEFT OUTER JOIN mytable2 pps
ON ppst.id = pps.id
LEFT OUTER JOIN mytable3 ps ON ps.id = pps.p_id
LEFT OUTER JOIN test p ON p.id = pps.prod_id
-- make join inner when data in product
LEFT OUTER JOIN product p2 ON p2.id = ppst.to_product_id
-- make join inner when data in product
LEFT OUTER JOIN filer f
ON f.id = ppst.did
-- WHERE ps.published_by IS NOT NULL
so - if the regular expression works the it should return
##########################
mytable2 pps
ON ppst.id
mytable3 ps ON ps.id
test p ON p.id
product p2 ON p2.id
filer f
ON f.id
##########################
ideally I would like ..
mytable2 pps ON ppst.id
mytable3 ps ON ps.id
test p ON p.id
product p2 ON p2.id
filer f ON f.id
I'm trying this expression but its not working
if record =~/join(.|\n\r\f)*=/i
puts record
I want to retrieve all text between "JOIN" and "="
sample text....
SELECT
*
FROM mytable ppst
LEFT OUTER JOIN mytable2 pps
ON ppst.id = pps.id
LEFT OUTER JOIN mytable3 ps ON ps.id = pps.p_id
LEFT OUTER JOIN test p ON p.id = pps.prod_id
-- make join inner when data in product
LEFT OUTER JOIN product p2 ON p2.id = ppst.to_product_id
-- make join inner when data in product
LEFT OUTER JOIN filer f
ON f.id = ppst.did
-- WHERE ps.published_by IS NOT NULL
so - if the regular expression works the it should return
##########################
mytable2 pps
ON ppst.id
mytable3 ps ON ps.id
test p ON p.id
product p2 ON p2.id
filer f
ON f.id
##########################
ideally I would like ..
mytable2 pps ON ppst.id
mytable3 ps ON ps.id
test p ON p.id
product p2 ON p2.id
filer f ON f.id
I'm trying this expression but its not working
if record =~/join(.|\n\r\f)*=/i
puts record
ASKER
if record =~/(?<=join).*?(?==)/i then
the above misses the 2 lines
LEFT OUTER JOIN mytable2 pps
ON ppst.id = pps.id
and it returns the whole line and not the text between join and =
Regards
the above misses the 2 lines
LEFT OUTER JOIN mytable2 pps
ON ppst.id = pps.id
and it returns the whole line and not the text between join and =
Regards
ASKER
just to correct my above posting ...
it does capture text between "join and =" and only returns data between those variables but does not return the first 2 lines - must be to do with = is on the next line
Regards
it does capture text between "join and =" and only returns data between those variables but does not return the first 2 lines - must be to do with = is on the next line
Regards
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
ASKER
Thanks
(?<=join).*?(?==)
then to remove the surplus spaces substitute pattern
\s{2,}
with a single space.
If you actually have the comments (starting with --) in the text, then you may need to remove those prior to the above.
I'm not a Ruby programmer, by the way; my specialty is regular expressions. Note also there is a Regular Expressions topic with other experts who contribute...