How to replace only the last occurrence in a string with a Perl regular expression?

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";

The code that generates the request ends with a simple s {where} {$ where} i, which replaces EVERY appearance with where.

I want to replace the top level of WHERE (the last occurrence of WHERE?) With "WHERE users.fake = -1" (in fact, with a more complex template, but that doesn't matter).

Any ideas?

Answer 1

Why do you want to build your SQL queries using hard coding strings, and then make a replacement for them? Is it something like

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

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

(, , ) , Data::Phrasebook::SQL make ?

, , , -

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

, , , "where", , , , , , , .

, , SQL-. , SQL - . , , select ... where user.name = 'where'.

Answer 2

-, Look-ahead regex

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

Answer 3

The right way to parse SQL queries is to do this with a parser and not use a regular expression.

see SQL :: Statement :: Structure - analyze and analyze the structure of SQL queries