How can I replace just the last occurrence in a string with a Perl regex?

Question

Ok, here is my test (this is not production code, but just a test to illustrate my problem)

my $string = <<EOS; # auto generated query
                SELECT
                        users.*
                        , roles.label AS role_label
                        , hr_orders.position_label
                        , deps.label AS dep_label
                        , top_deps.label AS top_dep_label
                FROM
                        users
                        LEFT JOIN $conf->{systables}->{roles} AS roles ON users.id_role = roles.id
                        LEFT JOIN (
                                SELECT
                                        id_user
                                        , MAX(dt) AS max_dt
                                FROM
                                        hr_orders
                                WHERE
                                        fake = 0
                                AND
                                        IFNULL(position_label, ' ') <> ' '
                                GROUP BY id_user
                        ) last_hr_orders ON last_hr_orders.id_user = users.id
                        LEFT JOIN hr_orders ON hr_orders.id_user = last_hr_orders.id_user AND hr_orders.dt = last_hr_orders.max_dt
                        $join
                WHERE
                        $filter
                ORDER BY
                        $order
                $limit
EOS

my $where = "WHERE\nusers.fake = -1 AND ";

$string =~  s{where}{$where}i;

print "result: \n$string";

Code, which generates the query, ends with simple s{where}{$where}i, which replaces EVERY occurence of where.

I want to replace top-level WHERE (last occurence of WHERE?) with 'WHERE users.fake = -1' (actually, with more complex pattern, but it doesn't matter).

Any ideas?

Answer 1

Why do you want to build your sql queries by hard-coding strings and then making replacements on them? Wouldn't something like

my $proto_query = <<'EOQ'
select ... where %s ...
EOQ

my $query = sprintf $proto_query, 'users.fake = -1 AND ...';

or (preferably, as it avoids a lot of issues your initial approach and the above has) using a module such as Data::Phrasebook::SQL make a lot of things easier?

If you really wanted to go for string substitutions, you're probably looking for something like

my $foo = "foo bar where baz where moo";
$foo =~ s/(.*)where/$1where affe and/;
say $foo; # "foo bar where baz where affe and moo"

That is, capturing as much as you can until you can't capture any more without not having a "where" immediately follow what you captured, and then inserting whatever you captured captured again, plus whatever modifications you want to make.

However, note that this has various limitations if you're using that to mangle SQL queries. To do things right, you'd have to actually understand the SQL at some level. Consider, for example, select ... where user.name = 'where'.

Answer 2

apparently, what I need was Look-ahead regex feature

my regex is

s{where(?!.*where)}{$where}is;

Answer 3

The right way to parse SQL queries is to do it using a parser and not using regex.

see SQL::Statement::Structure - parse and examine structure of SQL queries