Preg split multi SQL by semicolon in PHP

Question

i am trying split my multi SQL by preg, but it doesn works...

Code:

$multiSql = "
ALTER TABLE `my_table` CHANGE `typ` `typ` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0=none; 1=test1; 2=test2; 3=test3';

ALTER TABLE `my_table2`
  ADD `date` varchar(25) COLLATE utf8_czech_ci DEFAULT NULL AFTER `test`;
 
ALTER TABLE `my_table3` ADD `date` varchar(25) COLLATE utf8_czech_ci DEFAULT NULL AFTER `test`; ALTER TABLE `my_table3` ADD `test2` varchar(25) COLLATE utf8_czech_ci DEFAULT NULL AFTER `date`;

ALTER TABLE `my_table3` CHANGE `test2` `test2` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0=test; 1=test2;';
";

$sqlArray = preg_split('~\([^)]*\)(*SKIP)(*FAIL)(*F)|(?<=;)(?![ ]*$)~', trim($multiSql), -1, PREG_SPLIT_NO_EMPTY);

print_r($sqlArray);

Demo: https://ideone.com/nkvloT

I need output like this:

1. ALTER TABLE `my_table` CHANGE `typ` `typ` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0=none; 1=test1; 2=test2; 3=test3';
2. ALTER TABLE `my_table2` ADD `date` varchar(25) COLLATE utf8_czech_ci DEFAULT NULL AFTER `test`;
3. ALTER TABLE `my_table3` ADD `date` varchar(25) COLLATE utf8_czech_ci DEFAULT NULL AFTER `test`;
4. ALTER TABLE `my_table3` ADD `test2` varchar(25) COLLATE utf8_czech_ci DEFAULT NULL AFTER `date`;
5. ALTER TABLE `my_table3` CHANGE `test2` `test2` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0=test; 1=test2;';

I am using this pattern from diferend answer: ~$[^)]*$(*SKIP)(*FAIL)(*F)|(?<=;)(?![ ]*$)~ But it not works for my code.

Thanks for any help.

The fourth bird · Accepted Answer · 2022-07-14 17:16:01Z

1

The pattern can be written using a positive lookahead asserting whitespace chars until the end of the string instead.

You can also just use one of the two notations (*FAIL)(*F) instead of both.

\([^)]*\)(*SKIP)(*F)|(?<=;)(?=\h*$|\s*\bALTER TABLE\b)

Regex demo | PHP demo

Example

$multiSql = "
ALTER TABLE `my_table` CHANGE `typ` `typ` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0=none; 1=test1; 2=test2; 3=test3';

ALTER TABLE `my_table2`
  ADD `date` varchar(25) COLLATE utf8_czech_ci DEFAULT NULL AFTER `test`;
 
ALTER TABLE `my_table3` ADD `date` varchar(25) COLLATE utf8_czech_ci DEFAULT NULL AFTER `test`; ALTER TABLE `my_table3` ADD `test2` varchar(25) COLLATE utf8_czech_ci DEFAULT NULL AFTER `date`;

ALTER TABLE `my_table3` CHANGE `test2` `test2` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0=test; 1=test2;';
";

$sqlArray = array_map(function($x){
    return trim(preg_replace("/\R/", '', $x));
}, preg_split('~\([^)]*\)(*SKIP)(*F)|(?<=;)(?=\h*$|\s*\bALTER TABLE)~m', trim($multiSql), -1, PREG_SPLIT_NO_EMPTY));

print_r($sqlArray);

Output

Array
(
    [0] => ALTER TABLE `my_table` CHANGE `typ` `typ` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0=none; 1=test1; 2=test2; 3=test3';
    [1] => ALTER TABLE `my_table2`  ADD `date` varchar(25) COLLATE utf8_czech_ci DEFAULT NULL AFTER `test`;
    [2] => ALTER TABLE `my_table3` ADD `date` varchar(25) COLLATE utf8_czech_ci DEFAULT NULL AFTER `test`;
    [3] => ALTER TABLE `my_table3` ADD `test2` varchar(25) COLLATE utf8_czech_ci DEFAULT NULL AFTER `date`;
    [4] => ALTER TABLE `my_table3` CHANGE `test2` `test2` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0=test; 1=test2;';
)

answered Jul 14, 2022 at 17:16

The fourth bird

165k16 gold badges61 silver badges75 bronze badges

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

4 Comments

JeyC0b Over a year ago

Ohhh... Thank you so much. Would you still be able to modify the pattern in case it is not AFTER TABLE, but also INSERT, SELECT, etc.? SELECT * FROM xxx WHERE column = 'test ; test'; ALTER TABLE my_table CHANGE typ typ INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0=none; 1=test1; 2=test2; 3=test3'; Edit: okey it works for INSERT AND SELECT regex101.com/r/DOfJTl/2 Thx one more time.

The fourth bird Over a year ago

@JeyC0b Parsing SQL with a regex is error prone. Can you create a regex101 example with those strings?

JeyC0b Over a year ago

okey it works for INSERT AND SELECT regex101.com/r/DOfJTl/3 Thx one more time.

JeyC0b Over a year ago

Collectives™ on Stack Overflow

Preg split multi SQL by semicolon in PHP

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related