Get SQL table names from create table statements using preg_match

Question

I'm really bad with regex but I'm desperately trying to extract table names from SQL "create table" statements, I have something like this:

db.sql:

CREATE TABLE `users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
   PRIMARY KEY (`user_id`)
);

There could be multiple create table statements, I want to get all table names for each of these, I tried using this regex:

<?php

$sql = file_get_contents('db.sql');
preg_match('/create\s+table\s+\`?\w+`/i', $sql, $tables);
var_dump($tables);

It sort of works:

array:1 [▼
  0 => "CREATE TABLE `ps_advbisn_subscription`"
]

However I want the return the match without the "create table" part, I read about look aheads and behinds and tried this:

<?php

$sql = file_get_contents('db.sql');
preg_match('/(?<=create\s+table\s+\`?)(\w+)(?=`?)/i', $sql, $tables);
var_dump($tables);

But all I get is null. What am I missing here ?

u_mulder · Accepted Answer · 2017-11-10 18:37:41Z

1

All you need to do is to catch a table name, it is done with ():

preg_match('/create\s+table\s+\`?(\w+)`/i', $sql, $tables);
var_dump($tables[1])

answered Nov 10, 2017 at 18:37

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mike.void Over a year ago

but why are there 2 results ?

u_mulder Over a year ago

Because first item is a full string that matches regexp, second is a substring that you mark with () to be caught. If you add more () in your regexp - you will get more results.

Collectives™ on Stack Overflow

Get SQL table names from create table statements using preg_match

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related