CODEWITHSUNDEEP
phpregex
Codecraft
Codecraft

Reputation: 8296

PHP Regex to split an SQL field list

I'm looking for a regular expression to split a field list from an SQL SELECT statement.

I have already extracted the field list from the rest of the query, so I am left with a string that may look like:

field1,field2,field3,CONCAT(field1,field2) AS concatted,IF(field1 = field2,field3,field4) AS logic

I think the logical approach would be to split the string on a comma, provided the comma doesn't appear within parantheses, so I just need to find the right regular expression to do that...?

The end result of what I'm trying to achieve is to find out which fields appear to be straight selects from the table, and which ones are made from SQL expressions, so that I can decide later whether to filter them using WHERE or HAVING clauses.

Once I have the list of fields, I can check for the presence of the 'AS' keyword which although isn't perfect, should work with the way I write SQL.

(Bonus points for suggesting another way that differenciates between fields that are not expressions but are aliased, and expressions that may not be aliased, or may be but without using 'AS')

Upvotes: 6

Views: 892

Answers (2)

Gumbo
Gumbo

Reputation: 655309

When it comes to parsing irregular languages like SQL, you should always go with a proper parser instead of some regular expressions.

In your case you could use this SQL parser in PHP or PEAR’s SQL_Parser.

Upvotes: 0

aleroot
aleroot

Reputation: 72636

This regex should work : 

/,(?![^()]*+\\))/

An example implementation in PHP, here :

$vv = 'field1,field2,field3,CONCAT(field1,field2) AS concatted,IF(field1 = field2,field3,field4) AS logic';

$arr = preg_split ("/,(?![^()]*+\\))/", $vv);
foreach ($arr as &$value) {
    print($value);
}

Upvotes: 5

Related Questions