dongyan3616
dongyan3616
2015-02-04 16:30

数据库命名的正则表达式

已采纳

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • douzhe1264 douzhe1264 6年前

    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';
    
    点赞 评论 复制链接分享
  • dreamevil0002 dreamevil0002 6年前

    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.

    点赞 评论 复制链接分享

相关推荐