正则表达式或 LIKE 模式的转义函数 [英] Escape function for regular expression or LIKE patterns

问题描述

放弃阅读整个问题，我的基本问题是:
PostgreSQL 中是否有转义字符串中正则表达式字符的函数?

To forgo reading the entire problem, my basic question is:
Is there a function in PostgreSQL to escape regular expression characters in a string?

我已经浏览了文档，但找不到这样的功能.

I've probed the documentation but was unable to find such a function.

这里是完整的问题:

在 PostgreSQL 数据库中，我有一列包含唯一名称.我还有一个进程会定期将名称插入到该字段中，并且为了防止重复，如果需要输入一个已经存在的名称，它会在末尾附加一个空格和带有计数的括号.

In a PostgreSQL database, I have a column with unique names in it. I also have a process which periodically inserts names into this field, and, to prevent duplicates, if it needs to enter a name that already exists, it appends a space and parentheses with a count to the end.

即姓名、姓名(1)、姓名(2)、姓名(3)等

i.e. Name, Name (1), Name (2), Name (3), etc.

就目前而言，我使用以下代码查找要添加到系列中的下一个数字(用 plpgsql 编写):

As it stands, I use the following code to find the next number to add in the series (written in plpgsql):

var_name_id := 1;

SELECT CAST(substring(a.name from E'\((\d+)\)$') AS int)
INTO var_last_name_id
FROM my_table.names a
WHERE a.name LIKE var_name || ' (%)'
ORDER BY CAST(substring(a.name from E'\((\d+)\)$') AS int) DESC
LIMIT 1;

IF var_last_name_id IS NOT NULL THEN
    var_name_id = var_last_name_id + 1;
END IF;

var_new_name := var_name || ' (' || var_name_id || ')';

(var_name 包含我要插入的名称.)

(var_name contains the name I'm trying to insert.)

这暂时有效，但问题在于 WHERE 语句:

This works for now, but the problem lies in the WHERE statement:

WHERE a.name LIKE var_name || ' (%)'

此检查不会验证有问题的 % 是否为数字，也不会考虑多个括号，例如Name ((1))"之类的内容，如果任何一种情况都存在一个强制转换异常将被抛出.

This check doesn't verify that the % in question is a number, and it doesn't account for multiple parentheses, as in something like "Name ((1))", and if either case existed a cast exception would be thrown.

WHERE 语句确实需要更像:

WHERE a.r1_name ~* var_name || E' \(\d+\)'

但是 var_name 可能包含正则表达式字符，这导致了上面的问题:PostgreSQL 中是否有一个函数可以转义字符串中的正则表达式字符，所以我可以执行以下操作:

But var_name could contain regular expression characters, which leads to the question above: Is there a function in PostgreSQL that escapes regular expression characters in a string, so I could do something like:

WHERE a.r1_name ~* regex_escape(var_name) || E' \(\d+\)'

非常感谢任何建议，包括可能对我的重复名称解决方案进行修改.

Any suggestions are much appreciated, including a possible reworking of my duplicate name solution.

推荐答案

试试这样的方法如何，用 var_name 代替我的硬编码 'John Bernard':

how about trying something like this, substituting var_name for my hard-coded 'John Bernard':

create table my_table(name text primary key);
insert into my_table(name) values ('John Bernard'), 
                                  ('John Bernard (1)'), 
                                  ('John Bernard (2)'), 
                                  ('John Bernard (3)');


select max(regexp_replace(substring(name, 13), ' |(|)', '', 'g')::integer+1) 
from my_table 
where substring(name, 1, 12)='John Bernard' 
      and substring(name, 13)~'^ ([1-9][0-9]*)$';

 max
-----
   4
(1 row)

一个警告:我假设在此进程运行时单用户访问数据库(您的方法也是如此).如果情况并非如此，那么 max(n)+1 方法将不是一个好的方法.

one caveat: I am assuming single-user access to the database while this process is running (and so are you in your approach). If that is not the case then the max(n)+1 approach will not be a good one.

这篇关于正则表达式或 LIKE 模式的转义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！