Parse SQL SELECT clause text and create an array containing each column/expression

Question

I have a string as follows :

$str = "am_customers.customer_key,am_customers.add_dt,CONCAT(am_customers.first_name,'',am_customers.last_name) as name,am_customers.cell_phone,am_customers.crm_phase_key,am_customers.source,am_customers.location_key,am_customers.hub_key,am_customers.crm_priority,am_customers.update_dt";

I want to explode a string by comma. But the problem is with open and close brackets, When I try to explode a string by comma, I will get the result like as follows

Array
(
    [0] => am_customers.customer_key
    [1] => am_customers.add_dt
    [2] => CONCAT(am_customers.first_name
    [3] => ''
    [4] => am_customers.last_name) as name
    [5] => am_customers.cell_phone
    [6] => am_customers.crm_phase_key
    [7] => am_customers.source
    [8] => am_customers.location_key
    [9] => am_customers.hub_key
    [10] => am_customers.crm_priority
    [11] => am_customers.update_dt
)

But I want the result like as follows :

Array
(
    [0] => am_customers.customer_key
    [1] => am_customers.add_dt
    [2] => CONCAT(am_customers.first_name,'',am_customers.last_name) as name
    [3] => am_customers.cell_phone
    [4] => am_customers.crm_phase_key
    [5] => am_customers.source
    [6] => am_customers.location_key
    [7] => am_customers.hub_key
    [8] => am_customers.crm_priority
    [9] => am_customers.update_dt
)

Is there any way to do this like I want above?

preg_split using a regular expression that only includes commas outside of parentheses. — Devon Bessemer
– Devon Bessemer, Commented Jul 26, 2017 at 13:55

rideron89 · Accepted Answer · 2017-07-26 15:26:30Z

1

Inspired by the comment by @Devon you can achieve this with preg_match_all:

preg_match_all( '/[^C$,]*(?:(?:Cf\.|C(?!f)|\([^)]*$)[^C\(,]*)*/', $str, $matches );

Regex Source: http://www.perlmonks.org/?node_id=907316

I just tested this code and it seems to do what you are asking for:

$str = "am_customers.customer_key,am_customers.add_dt,CONCAT(am_customers.first_name,'',am_customers.last_name) as 
             name,am_customers.cell_phone,am_customers.crm_phase_key,  am_customers.source,am_customers.location_key,am_customers.hub_key,
             am_customers.crm_priority,am_customers.update_dt";

$matches = [];
preg_match_all( '/[^C\(,]*(?:(?:Cf\.|C(?!f)|\([^)]*\))[^C\(,]*)*/', $str, $matches );

/*
* Trims each match, removes empty string matches, and resets array keys.
*
* Source: http://php.net/manual/en/function.array-filter.php#111091
*/
$clean = array_map( 'trim', $matches[0] );
$clean = array_filter( $clean, 'strlen' );
$clean = array_values( $clean );

var_dump( $clean );

Documentation

array_filter: http://php.net/array_filter

array_map: http://php.net/array_map

array_values: http://php.net/array_values

preg_match_all: http://php.net/preg_match_all

edited Jul 26, 2017 at 15:26

answered Jul 26, 2017 at 15:11

rideron89

5313 silver badges15 bronze badges

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

1 Comment

Punam Over a year ago

Hi @rideron89, preg_match_all( '/[^C(,]*(?:(?:Cf\.|C(?!f)|([^)]*))[^C(,]*)*/', $str, $matches ); Above line is giving me an error. something is wrong in that i think.

mickmackusa · Accepted Answer · 2024-07-25 09:53:42Z

Using regex will not be as reliable as a legitimate SQL parser, but if you don't want to add a library to your project, here is a pattern (based on this answer) which endeavors to split on commas not found within potentially nested parenthetical expressions.

I'll be very clear that this pattern can be easily broken/fooled by an expression or value that contains a parentheses as part of a value (not part of function encapsulation syntax).

Code: (Demo)

$str = "am_customers.customer_key,am_customers.add_dt,CONCAT(am_customers.first_name,'',am_customers.last_name) as name,am_customers.cell_phone,am_customers.crm_phase_key,am_customers.source,am_customers.location_key,am_customers.hub_key,am_customers.crm_priority,am_customers.update_dt";
$str .= ",CONCAT(REPLACE(foo, LOWER(bar), UPPER(colval))) replaced, fooey, AVG(foo), PI()";

$regex = <<<REGEX
/
(?>
   ([^,]*\((?:(?>[^()]+)|(?1))*\)[^,]*)
   |[^,]+
)
\K,\s*
/x
REGEX;

var_export(
    preg_split($regex, $str)
);

Output:

array (
  0 => 'am_customers.customer_key',
  1 => 'am_customers.add_dt',
  2 => 'CONCAT(am_customers.first_name,\'\',am_customers.last_name) as name',
  3 => 'am_customers.cell_phone',
  4 => 'am_customers.crm_phase_key',
  5 => 'am_customers.source',
  6 => 'am_customers.location_key',
  7 => 'am_customers.hub_key',
  8 => 'am_customers.crm_priority',
  9 => 'am_customers.update_dt',
  10 => 'CONCAT(REPLACE(foo, LOWER(bar), UPPER(colval))) replaced',
  11 => 'fooey',
  12 => 'AVG(foo)',
  13 => 'PI()',
)

Collectives™ on Stack Overflow

Parse SQL SELECT clause text and create an array containing each column/expression

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related