phpsplittext-parsingsql-parser

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


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?


Solution

  • 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