如何用Perl正则表达式替换字符串中的最后一个匹配项？

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.

生成查询的代码以简单的s {where} {$ where} i结尾，它取代了每次出现的地方。

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).

我想用'WHERE users.fake = -1'替换*WHERE（WHERE的最后一次出现？）（实际上，模式更复杂，但没关系）。

Any ideas?

有任何想法吗？

3 个解决方案

#1

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

为什么要通过硬编码字符串然后对它们进行替换来构建sql查询？不会像

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?

或者（最好，因为它避免了很多问题，你的初始方法和上面的方法）使用Data :: Phrasebook :: SQL等模块使很多事情变得更容易？

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'.

但请注意，如果您使用它来破坏SQL查询，则会有各种限制。要做正确的事，你必须在某种程度上真正理解SQL。例如，考虑选择... where user.name ='where'。

#2

apparently, what I need was Look-ahead regex feature

显然，我需要的是Look-ahead正则表达式功能

my regex is

我的正则表达式是

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

#3

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

解析SQL查询的正确方法是使用解析器而不使用正则表达式。

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

请参阅SQL :: Statement :: Structure - 解析并检查SQL查询的结构

#1