Regular expression for database naming

Question

I've thousand of queries I need to execute from a text file.

See bellow a kind of query I have:

SELECT * 
    FROM T1 A 
    INNER JOIN DB2.dbo.CI_T1 B ON A.id= B.id 
    LEFT OUTER JOIN T3 C ON B.id = C.id
    WHERE ...

Some of tables are well named (database.owner.table_name), and some other no (table_name).

How can I use a regex to update each query to replace unqualified instances of table_name with database.owner.table_name?

I write fiddle to test it: http://www.phpliveregex.com/p/9SH and http://www.phpliveregex.com/p/9SF

What language are you working with to do the replacement? Do the tables all have aliases? If not, doing this replacement in the `ON` clauses would be very difficult. — Michael Berkowski, Feb 04 '15 at 16:33

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

If it were me I wouldn't attempt to use a regex. While it is trivial for a simple SELECT like you provided (although you seem to be having some difficulties) it won't be long before you find how difficult it is to parse a programming language using a simple regex.

I'd start with an SQL parser.

edited May 23 '17 at 12:05

Community

1
1

answered Feb 04 '15 at 17:58

symcbean

47,736
6
59
94

score 0 · Accepted Answer · answered Feb 09 '15 at 12:04

0

In the end I use following regex with preg_replace function:

$regex = '#(FROM)\s+((?!\.)[[:alnum:]_]+)\s+(\w*)\s*(INNER|LEFT|WHERE|\))#i';
$regex2 = '#(JOIN)\s+((?!\.)[[:alnum:]_]+)\s+(\w*)\s*(ON)#i';

answered Feb 09 '15 at 12:04

Fractaliste

5,777
11
42
86

Regular expression for database naming

2 Answers2