Take values from SQL string with regexp

Question

I have two strings

left join orderitem on order.id = orderitem.orderid right join product on product.id = orderitem.productid join orders on customers.customerid=orders.customerid;

what i want from first string:

=> left join orderitem on order.id = orderitem.orderid
=> right join product on product.id = orderitem.productid 
=> join orders on customers.customerid=orders.customerid

second string:

join orderitem on order.id = orderitem.orderid right join product on product.id = orderitem.productid join orders on customers.customerid=orders.customerid group by id;

what i want from second string:

=> join orderitem on order.id = orderitem.orderid
=> right join product on product.id = orderitem.productid
=> join orders on customers.customerid=orders.customerid

That's what i did... but it's not working properly...

/([inner|left|right|full]*?\s.+?(?:inner|left|right|full|join|;))/

Stop that, it's a waste of time for a bad result, and use an SQL parser, try something like: github.com/cryodex/sql-parser — Casimir et Hippolyte
– Casimir et Hippolyte, Commented Jan 28, 2017 at 20:01

grail · Accepted Answer · 2017-01-28 20:01:44Z

2

So I am not sure if you just want a solution or one that fits what you have already tried??

I found the following provided the desired output:

/(.*?= ?[^ ;]*) ?/

If you need a solution using your current regex option I might need longer to work it out :)

answered Jan 28, 2017 at 20:01

grail

9301 gold badge6 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user9903 Over a year ago

also for other people stumbling across this answer, you can use RegExr to test your regular expressions. Here's the examples from the questions along with the solution: regexr.com/3f611

user9903 · Accepted Answer · 2017-01-28 20:08:28Z

2

As pointed out in the other answers, using regular expressions may not be the best approach.

There are SQL parsers for Ruby that you can use like sql-parser

Using it, it would be easy to parse your strings.

In the code for sql-parser you can see that it knows how to parse different types of JOIN statements

answered Jan 28, 2017 at 20:08

user9903

Comments

lorefnon · Accepted Answer · 2017-01-28 19:57:28Z

While attempting to parse SQL using regular expressions is impossible in general, for this specific example you can try:

/(((inner|left|right|full)\s+)?join\s+[^=]*=\s*\S+\b)/

> regexp = /(((inner|left|right|full)\s+)?join\s+[^=]*=\s*\S+\b)/

> str1 = "left join orderitem on order.id = orderitem.orderid right join product on product.id = orderitem.productid join orders on customers.customerid=orders.customerid;"
 => "left join orderitem on order.id = orderitem.orderid right join product on product.id = orderitem.productid join orders on customers.customerid=orders.customerid;"

> str1.scan(regexp).map(&:first)
 => ["left join orderitem on order.id = orderitem.orderid", "right join product on product.id = orderitem.productid", "join orders on customers.customerid=orders.customerid"]

Collectives™ on Stack Overflow

Take values from SQL string with regexp

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related