regex to extract SQL select fields

[stuartmarsh]

Hi all,

I'm looking for a regex to extract fields from a SQL string.

SQL: SELECT col1, col2, col3 FROM table WHERE col1='A';
Expected results:
Array[0] = "col1"
Array[1] = "col2"
Array[2] = "col3"

Or at the very least a regex to get the string between SELECT and FROM.  Then I split it down and extract them programmatically.

SQL: SELECT col1, col2, col3 FROM table WHERE col1='A';
Expected results:
Array[0] = "col1, col2, col3"

 

Cheers,

Stu

[MadTechie]

Try this

<?php
$SQL = "SELECT col1, col2, col3 FROM table WHERE col1='A';";
if (preg_match('/SELECT (.*?) FROM /i', $SQL, $regs)) { //extracts the col1, col2, col3
$result = explode(",",str_replace(", ",",",$regs[1])); //changes ", " to "," (without the quotes) and puts into array
}
var_dump($result);//display array details

 

EDIT: added commented

[cags]

At it's most basic (and not using regex)...

 

$input = "SELECT col1, col2, col3 FROM table WHERE col1='A'";
$fields = substr($input, 6, strpos($input, "FROM") - 6);
$fields = explode(', ', $fields);

Somebody else was attempting to do this recently. The issue the last member had was if there was any functions used on the fields (such as SUBSTR as it uses commas), is that going to be a problem for you.

 

EDIT: MadTechie replied whilst I was writting. He brings up an important point though I guess, theres no guarantee that the SQL statement will have a gap between the comma and next field name. You can fix that on my code by using the same explode statement he did.

[MadTechie]

Unless they have 2 spaces!

but you can't  just replace the spaces, cleaner solution would be

<?php
$SQL = "SELECT col1, col2,           col3,col4  FROM table WHERE col1='A';";
if (preg_match('/SELECT (.*?) FROM /i', $SQL, $regs)) {
$result = explode(",",$regs[1]);
$result = array_map('trim', $result);
}
var_dump($result);

 

But I'm too lazy to type it

 

 

D'oh!

[cags]

Or since we're in the Regular Expressions board...

 

preg_replace("#\s+#s", "", $regs[1]);

 

[salathe]

Is there any need to cater for AS (foo AS bar) or computed columns (COUNT(foo) AS bar)?

[MadTechie]

Valid Point

If so I would use the same logic but create a function called fieldFilter

$result = array_map('fieldFilter', $result);

function fieldFilter($str){
$str = trim($str);
//filter AS

//Filter backticks

//etc
return $str;
}

[cags]

Is there any need to cater for AS (foo AS bar) or computed columns (COUNT(foo) AS bar)?

That's basicly what I was eluding to in my first post as thats what the other member was attempting to do (well they were only counting the fields at the time). As MadTechie says, the initial part shouldn't require changing, simply the split parts will need parsing out.

 

EDIT: Exluding the problem I mentioned earlier of commas being used in functions.

[salathe]

What about IFNULL(blah, 'foo') AS foolah or some other "column" containing commas?

[cags]

What about it? I already mentioned it (twice) as an exception...

[salathe]

I guess I'm still in that other thread (assumed the OPs were one and the same).  Well, if the OP wants to cater for those slightly more complex fields then he only has to say so.

[MadTechie]

Well, if the OP wants to cater for those slightly more complex fields then he only has to say so.

Indeed, anything we try say is going to be guess work but I added a pre-filter, this may not be the best route but its workable!

<?php
$SQL = "SELECT col1, col2,           col3,col4 , IFNULL(blah, 'foo') AS foolah, foo AS bar1, COUNT(foo) AS bar2  FROM table WHERE col1='A';";
if (preg_match('/SELECT (.*?) FROM /i', $SQL, $regs)) {
$regs[1] = preFilter($regs[1]);
$result = explode(",",$regs[1]);
$result = array_map('fieldFilter', $result);
}
var_dump($result);


function fieldFilter($str){
//filter out spaces and back ticks
$str = trim($str," `");
//filter out AS
$str = preg_replace('/^.*as\s+/i', '', $str);

//etc
return $str;
}
function preFilter($str){
//filter out any () contents
$str = preg_replace('/\(.*?\)/i', '', $str);

//etc
return $str;
}

[cags]

I know we're going somewhat over the top since the OP hasn't specified, but I've been trying to learn up on look-(ahead|behind|around) assertions, this just occured to me...

 

$pattern = "#(?=[^\)]*(?:\(|$))\s*,\s*#s";
$fields = preg_split($pattern, $subject);

Then filter the $fields array for the AS keyword using MadTechies array_map approach. What do you think?!

[stuartmarsh]

Hi all,

Thanks for the replies and code.

The code from MadTechie looks good.

The code looks good but there is just one more consideration.  When using the AS clause I use either ' or [] around the alias name (I.e. col1 AS [foo 1], col2 AS 'foo 2').

Can I use trim to filter out ', [, & ]?  I tried adding them to the charlist but it only removes the end apostrophe and "]".

 

Thanks all!

[MadTechie]

add  ][ to the time

Nice RegEx Cags

[stuartmarsh]

Using your example I changed the sql to

$SQL = "SELECT col1, col2,           col3,col4 , IFNULL(blah, 'foo') AS foolah, foo AS [bar1], COUNT(foo) AS 'bar2'  FROM table WHERE col1='A';";

and

$str = trim($str," `");

to

$str = trim($str," `\]['");

.

Now it removes the ] and the end ' for array keys 5 & 6 respectively.

But I'm still left with a [ on array key 5 and a start apostrophe on array key 6.

[stuartmarsh]

Using your example I changed the sql to

Code: [select]

$SQL = "SELECT col1, col2,          col3,col4 , IFNULL(blah, 'foo') AS foolah, foo AS [bar1], COUNT(foo) AS 'bar2'  FROM table WHERE col1='A';";

and

Code: [select]

$str = trim($str," `");

to

Code: [select]

$str = trim($str," `\]['");

.

Now it removes the ] and the end ' for array keys 5 & 6 respectively.

But I'm still left with a [ on array key 5 and a start apostrophe on array key 6.

[MadTechie]

ahh that because of the as

heres a function update

function fieldFilter($str){
//filter out AS
$str = preg_replace('/^.*as\s+/i', '', $str);
//filter out spaces and back ticks and ][
$str = trim($str," ][`'");
return $str;
}

[cags]

Why trim before and after the preg_replace, surely you could just do it all after?  :-\

[MadTechie]

Why trim before and after the preg_replace, surely you could just do it all after?  :-\

I Mean, I have no idea what your talking about

Oh look a bug it says I just edited my last post but I didn't honest!

[cags]

[ot]Hmm... Looks like another glitch from the update... Perhaps we should report it... [/ot]

[MadTechie]

[ot]No no, I'm sure it will resolve itself [/ot]

[stuartmarsh]

Bingo!

This works exactly the way I need it.

Thanks MadTechie.

 

[MadTechie]

Coolie, I'll take all the credit 

Not forgetting cags & salathe who pointed out issue and solutions.