CODEWITHSUNDEEP
rubyregex
Yury Matusevich
Yury Matusevich

Reputation: 998

Take values from SQL string with regexp

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|;))/

Upvotes: 0

Views: 65

Answers (3)

user9903
user9903

Reputation:

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

Upvotes: 2

grail
grail

Reputation: 930

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 :)

Upvotes: 2

lorefnon
lorefnon

Reputation: 13105

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"]

Upvotes: 1

Related Questions