Replace string with regex only if-CodePudding

I have two strings:

select * from tbl1 where id = %d and name = %s limit %d,%d;
select * from tbl1 where id = %d and name = %s limit %d;

And I would to replace all %d, %s with '%d', '%s' before the limit statement:

select * from tbl1 where id = '%d' and name = '%s' limit %d,%d;
select * from tbl1 where id = '%d' and name = '%s' limit %d;

Currently I use two operations (first I add the quotes then I remove them from "Limit"):

function addQuotes($q) {
    return preg_replace('/%./', "'$0'", $q);
}

function fixLimit($q) {
    return preg_replace("/LIMIT\s '(%d)'(\s*(,)\s*'(%d)')/i", 'LIMIT $1$3$4', $q);
}

$query = 'select * from tbl1 where id = %d and name = %s limit %d,%d;';
$query = addQuotes($query);
$query = fixLimit($query);

Can I do it with only one regex?

CodePudding user response：

What you can do to get the desired output without having to “fix”, would be only applying the quotes to the part before LIMIT:

<?php

function transform(string $query): string
{
    $parts = explode(
        separator: 'limit',
        string: $query,
    );

    $parts[0] = preg_replace(
        pattern: '/%./',
        replacement: "'$0'",
        subject: $parts[0],
    );

    return implode(
        separator: 'limit',
        array: $parts,
    );
}

echo transform(
    query: 'select * from tbl1 where id = %d and name = %s limit %d,%d;',
);

// select * from tbl1 where id = '%d' and name = '%s' limit %d,%d;

CodePudding user response：

Two ideas (assuming query ends in semicolon)

By use of a lookahead -> regex101 demo
```
%.(?=[^;]*?\slimit\b)
```
By skipping the part from limit -> demo
```
 \slimit. (*SKIP)(*F)|%.
```

For caseless matching use with i modifier.