0

In this regex I'm trying to extract the list of tables from a SQL select statement. It works fine with one exception, if the statement ends with the table name there is no match. For example:

Given he regex:

(?:from|join)\s+(.*?)(?:\)|\s+(?:where|on|inner|outer|full|left|right|join|\s*$))

and given this string:

"select xxxx  from table1 t1, table2, table3 t3 "  (note the last space)

The match is:

"table1 t1, table2, table3 t3"

But given this string:

"select xxxx  from table1 t1, table2, table3 t3"  (without last space)

There's no match. How to make this work?

ps0604
  • 1,227
  • 23
  • 133
  • 330
  • 2
    Wow, that's going to be tough. What about fully-qualified, escaped table names like `[DataBase].[dbo].[TableName]` (SQL Server) or `\`TableName\`` in MySQL (just for two examples your regex won't hit). Subqueries as well (`select * from (select * from table)`). I'm afraid regular expressions are not the right tool for this job. – Cᴏʀʏ May 15 '15 at 20:29
  • I would rather parse it that try to match it (not saying it's not possible but this would need some work at least) – Denys Séguret May 15 '15 at 20:30
  • 2
    SQL is not a regular language so... parse it. Keep your sanity. Don't follow Cthulu. ;) – jdphenix May 15 '15 at 20:41
  • I just need the table names, do I still need to parse it? if yes, how to do that? – ps0604 May 15 '15 at 20:45
  • 1
    Can you guarantee that your input will stay simple? For instance, might you have to extract the tables from something of the form `select count(*) from ( select foo.bar, baz.foo from foo, (select foo from herp) baz)`? If so, matching just won't do it. – Politank-Z May 15 '15 at 20:56

1 Answers1

1

RegEx isn't very good at this, as it's a lot more complicated than it appears:

  • What if they use LEFT/RIGHT INNER/OUTER/CROSS/MERGE/NATURAL joins instead of the a,b syntax? The a,b syntax should be avoided anyway.
  • What about nested queries?
  • What if there is no table (selecting a constant)
  • What about line breaks and other whitespace formatting?
  • Alias names?

What you can do is an sql parser, and there is a good one Hrer.

See more answers in this post.

Community
  • 1
  • 1
Maraboc
  • 10,550
  • 3
  • 37
  • 48