Parse an SQL CALL statement and create an array with its parameters

Question

I am wanting to break down a string in to different parts and because of the nature of the string I cant quite get my head around how I could go about doing this or if its possible.

Below is the string that I am trying to break up:

"'1,2,3,4','2,4,5,6,4',NULL, NULL,NULL ,'2,3,4,5', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL"

I would like it like:

'1,2,3,4',
'2,4,5,6,4',
NULL,
NULL,
'2,3,4,5'

I was thinking of dooing something like this:

$test = 'CALL sp_advert_search_load(\'1,3,4,5\',\'2,4,3\',NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)';
$test1 = explode( '(',$test);
$test1 = explode( ',',$test1[1]);

foreach ($test1 as $food) {
    echo $food."<br />";
}

Then trying to build the array back into strings using the ' and a start and end point somehow.

Answer 1

You seem to have 2 problems, but this splits it into two parts anyway.

First is to extract the parameters part from the CALL, this finds the first ( and takes the rest of the string. Then it uses rtrim() to strip off the trailing ) (also spaces in case any are there).

The second part is to split the string into each parameter. As it is now effectively a CSV, I use str_getcsv() to split with a ' as the enclosure to keep the groups together...

$test = 'CALL sp_advert_search_load(\'1,3,4,5\',\'2,4,3\',NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)';

$params = rtrim(substr($test, strpos($test, "(") + 1), " )");
print_r(str_getcsv($params, ",", "'"));

gives...

Array
(
    [0] => 1,3,4,5
    [1] => 2,4,3
    [2] => NULL
    [3] =>  NULL
    [4] =>  NULL
    [5] =>  NULL
    [6] =>  NULL
    [7] =>  NULL
    [8] =>  NULL
    [9] =>  NULL
    [10] =>  NULL
    [11] =>  NULL
    [12] =>  NULL
    [13] =>  NULL
    [14] =>  NULL
    [15] =>  NULL
    [16] =>  NULL
    [17] =>  NULL
    [18] =>  NULL
    [19] =>  NULL
    [20] =>  NULL
    [21] =>  NULL
    [22] =>  NULL
    [23] =>  NULL
    [24] =>  NULL
    [25] =>  NULL
    [26] =>  NULL
    [27] =>  NULL
    [28] =>  NULL
    [29] =>  NULL
    [30] =>  NULL
)

Answer 2

Because I'm bored, just extract the stuff between the parentheses and then eval it as an array:

preg_match('/\(.*\)/', $test, $match);
eval("\$result = array$match[0];");

Or:

preg_match('/\((.*)\)/', $test, $match);
eval("\$result = [$match[1]];");

These will give you actual NULL values not string "NULL" so you will only see them in a var_dump.

Answer 3

Isolate the substring inside the CALL statement and parse it with str_getcsv().

Code: (Demo)

$test = 'CALL sp_advert_search_load(\'1,3,4,5\',\'2,4,3\',NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)';

var_export(
    str_getcsv(
        preg_replace(
            '/[^(]*\((.*)\)/',
            '$1',
            $test
        ),
        ',',
        "'"
    )
);

Notice, though, that you will need to left trim some of your NULL values.

array (
  0 => '1,3,4,5',
  1 => '2,4,3',
  2 => 'NULL',
  3 => ' NULL',
  4 => ' NULL',
  5 => ' NULL',
  6 => ' NULL',
  7 => ' NULL',
  8 => ' NULL',
  9 => ' NULL',
  10 => ' NULL',
  11 => ' NULL',
  12 => ' NULL',
  13 => ' NULL',
  14 => ' NULL',
  15 => ' NULL',
  16 => ' NULL',
  17 => ' NULL',
  18 => ' NULL',
  19 => ' NULL',
  20 => ' NULL',
  21 => ' NULL',
  22 => ' NULL',
  23 => ' NULL',
  24 => ' NULL',
  25 => ' NULL',
  26 => ' NULL',
  27 => ' NULL',
  28 => ' NULL',
  29 => ' NULL',
  30 => ' NULL',
)