Regular Expression to parse SQL Structure

Question

I am trying to parse the MySQL data types returned by "DESCRIBE [TABLE]".

It returns strings like:

int(11)
float
varchar(200)
int(11) unsigned
float(6,2)

I've tried to do the job using regular expressions but it's not working.

PHP CODE:

$string = "int(11) numeric";<br/>
$regex = '/(\w+)\s*(\w+)/';<br/>
var_dump( preg_split($regex, $string) );<br/>

Why do you need to use regular expressions for this? What do you want to ultimately have once you've parsed the output? — JYelton
– JYelton, Commented May 26, 2010 at 21:59
parse it how? What do you want the end result to be? The contents of the parenthesis? — arnorhs
– arnorhs, Commented May 26, 2010 at 22:01

Marco Mariani · Accepted Answer · 2010-05-26 22:05:31Z

4

Query the information schema for introspection of tables and columns, and more.

answered May 26, 2010 at 22:05

Marco Mariani

13.8k7 gold badges42 silver badges55 bronze badges

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

Comments

Bill Karwin · Accepted Answer · 2010-05-26 22:18:49Z

3

I wrote code to do this in Zend_Db_Adapter_Mysqli::describeTable().
It handles varchar, char, decimal, float, and all MySQL integer types.

I won't post the code here because of StackOverflow's CC-wiki license policy, but go check it out at that link.

mysql_fetch_field() only reports data types for query result sets, not persisted tables.

INFORMATION_SCHEMA is another option, but MySQL's early implementation of it has incredibly poor performance, which makes it hard to use in a web app that needs quick response time. Some changes in MySQL 5.1.23 tried to improve the performance, but some people still report problems.

edited May 26, 2010 at 22:18

answered May 26, 2010 at 22:10

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

3 Comments

Frank Farmer Over a year ago

Purely out of curiosity, does CC-wiki really conflict with the BSD license in a major way?

Bill Karwin Over a year ago

The CC Share-Alike license that SO uses requires attribution and it also requires that any work derived from the CC licensed code must also use a similar license. Not a conflict per se, but it muddies the terms of use.

Miro Over a year ago

This is exactly what I was looking for. I've already tested and it works PERFECTLY! :)

Byron Whitlock · Accepted Answer · 2010-05-26 22:02:15Z

3

Why not just pull meta-data from the fields directly?

...
$meta = mysql_fetch_field($result, $i);
echo $meta->type;
echo $meta->max_length;

answered May 26, 2010 at 22:02

Byron Whitlock

54.2k29 gold badges128 silver badges170 bronze badges

Collectives™ on Stack Overflow

Regular Expression to parse SQL Structure

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related