Hi,

Programming language: Delphi with pcre-compatible component

The question is: Split the SQL-statement items to:

-type (select|insert|delete|truncate table)

-distinct mode if exist (is disitinct|is not distinct)

-top if exist (top statement - rows or persent)

-join if exist (type of join, cross-table and cross-condition)

Thats what I have to this moment:

and this is the regex I made to this moment:

(?<Type>(select|insert|update|delete|truncate\s*table))\s*(?<Distinct>distinct)?\s*(Top\s*(?<Top>[\d]*))\s*.*from\s*(?<Table>[\w\d\[\]\.()'"]+)\s*((?<JoinType>|inner|outer|left|right)?\s*join\s*(?<JoinTable>[\w\d\[\]\.'"]+)\s*on\s*(?<JoinCond>[\w\d\[\]\.()'"]+\s*[=<>!]{1,2}\s*[\w\d\[\]\.'"]+))?\s*

and example SQL

select top 1000 dbo.[Database1].ForeingTable.Column1 as Alias1, 'N\A' As Alias2, 0 as Alias3, max(Column2) as Alias3, Column3 from table1 join table2 on table1.pk=table2.fk inner joint table3 on table1.pk = table2.fk order ... group ..

And the problem is what i catch only first of join conditions, not all

I know, I write sample sql statement very hardly

Thanks for all of You, Anton

That doesn't match for me against your sample.  Please provide a real-world sql example without the "..." blocks.

---

Thanks for reply!

1. There is an error in regex - TOP clause is marked as requred, not as optional, fixed as

(?<Type>(select|insert|update|delete|truncate\s*table))\s*(?<Distinct>distinct)?\s*(Top\s*(?<Top>[\d%]*))?\s*.*from\s*(?<Table>[\w\d\[\]\.()'"]+)\s*((?<JoinType>|inner|outer|left|right)?\s*join\s*(?<JoinTable>[\w\d\[\]\.'"]+)\s*on\s*(?<JoinCond>[\w\d\[\]\.()'"]+\s*[=<>!]{1,2}\s*[\w\d\[\]\.'"]+))?\s*

The real world statement:

SELECT ProductName, MAX(OrderDate) AS Max_OrderDate, MAX(StoragePoint + N'/' + StorageSubPoint) AS Current_StoragePoint, NULL AS OrderID, Persons.PersonName AS OrderPerson FROM dbo.Orders INNER JOIN dbo.Persons on Orders.PersonID = Persons.PersonID INNER JOIN dbo.Storage on OrdersID = Storage.OrdersStorageID WHERE     (ProductID IS NOT NULL)

Your pattern still does not match for me with your sample, please show what matches you would like from your sample.

---

Ilway,

I think the problem might be that the sub-pattern that finds the joins (I've split the following over several lines for clarity):

(
    (?<JoinType>|inner|outer|left|right)?
    \s*
    join
    \s*
    (?<JoinTable>[\w\d\[\]\.'"]+)
    \s*
    on
    \s*
    (?<JoinCond>[\w\d\[\]\.()'"]+\s*[=<>!]{1,2}\s*[\w\d\[\]\.'"]+)
)?

ends with a '?' which means that it will match at most one instance.

Of course, if you convert the whole thing to be:

(?<Type>(select|insert|update|delete|truncate\s*table))
\s*
(?<Distinct>distinct)?
\s*
(Top\s*(?<Top>[\d%]*))?
\s*
.*from\s*
(?<Table>[\w\d\[\]\.()'"]+)
(
    \s*
    (?<JoinType>|inner|outer|left|right)?
    \s*
    join
    \s*
    (?<JoinTable>[\w\d\[\]\.'"]+)
    \s*
    on
    \s*
    (?<JoinCond>[\w\d\[\]\.()'"]+\s*[=<>!]{1,2}\s*[\w\d\[\]\.'"]+)
)*
\s*

(note the '\s*' has been moved from before the 'join' expression and the whole group quantifier has been changed from '?' to '*') then this will also work and will capture each join clause BUT will only return the LAST of the join clauses unless you are using the .NET regex engine which returns all of the captures for a match group. If this is a problem, then I would suggest using the above pattern to return the whole of the SQL statement that you are interested in, and use the 'join' clase sub-pattern as a second pass to return multiple matches, one for each join clause.

Susan

 

Thanks, Susan! I think to split my regex into one (so called "base") script - to match statement structure and, by the type returned (select, insert or so) - to join, and, possibly, column match.

Thank You Again for detailed answer!