oracle 如何在oracle中转义特殊的正则表达式字符？

how to escape special regular expression characters in oracle?

提问by SWilk

How to escape special characters of regular expression pattern in Oracle?

如何在 Oracle 中转义正则表达式模式的特殊字符？

For example I need a function which traslates

例如，我需要一个函数来翻译

some.string[with(special)reg-exp]characters

some.string[with(special)reg-exp]characters

to

到

some\.string\[with\(special\)reg\-exp\]characters.

some\.string\[with\(special\)reg\-exp\]characters.

In PHP I would use preg_escape(). Is there Oracle counterpart?

在 PHP 中，我会使用 preg_escape()。有oracle对应吗？

Why I am doing this?

我为什么要这样做？

I am trying to write a pl/sql function that checks if a stringis on the list,of,string,elements.

我正在尝试编写一个 pl/sql 函数来检查 astring是否在list,of,string,elements.

Here is my code:

这是我的代码：

CREATE OR REPLACE 
FUNCTION list_contains(needle_    IN VARCHAR2,
                       haystack_  IN VARCHAR2,
                       separator_ IN VARCHAR2 DEFAULT ',')
RETURN INTEGER
  IS
BEGIN
  IF regexp_like(haystack_, '(^|' || separator_ || ')' || needle_ || '(' || separator_ || '|$)') THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END list_contains;

The function basicly works:

该功能基本上有效：

list_conains('eve','john,eve,maria,steve') => 1

The problem is when I try to call it with weird values of needle_or separator_, such as .or other strings that have special meaning in regular expressions.

问题是当我尝试使用needle_or 的奇怪值调用它时separator_，例如.或 其他在正则表达式中具有特殊含义的字符串。

list_conains('eve','john.maria.steve','.') => 1

list_conains('eve','john.maria.steve','.') => 1

As you see, there is no eve on the list, but the .matches the tletter of the steve name, so the function incorrectly returns 1.

如您所见，列表中没有 eve，但.与t史蒂夫姓名的字母匹配，因此该函数错误地返回1.

I know that I can replace the dots manually, but there still are plenty other regex special characters that will interfere and I would rather not try to list all the characters myself.

我知道我可以手动替换点，但仍有很多其他正则表达式特殊字符会干扰，我宁愿不尝试自己列出所有字符。

How to escape the needle_ and separator_?

如何逃脱needle_和separator_？

回答by Noel

If i understand your question correctly, you don't need regular expressions for this. You can use the simple LIKE.

如果我正确理解你的问题，你不需要正则表达式。您可以使用简单的 LIKE。

IF separator_ || haystack_ || separator_ LIKE '%'||separator_||needle_||separator_||'%' THEN
   RETURN 1;
ELSE
   RETURN 0;
END IF;

Edit: If %or _itself is the separator_, then it necessary to escape them.

编辑：如果%或_它本身是separator_，则有必要对它们进行转义。

IF separator_ = '%' OR separator_ = '_'
THEN
  separator_ := '\' || separator_;
END IF;

IF separator_ || haystack_ || separator_ LIKE
     '%' || separator_ || needle_ || separator_ || '%' ESCAPE '\'
THEN
  RETURN 1;
ELSE
  RETURN 0;
END IF;

回答by Emma

I think it might be easiest to just escape the separator in your regexp statement. Try this, it seemed to work for me:

我认为在正则表达式语句中转义分隔符可能是最简单的。试试这个，它似乎对我有用：

  IF regexp_like(haystack_, '(^|\' || separator_ || ')' || needle_ || '(\' || separator_ || '|$)') THEN

All I changed was the slash in front of the separators in your string construction.

我所做的只是更改字符串构造中分隔符前面的斜杠。

回答by ten.bv

The following characters must be escaped: \ ^ . $ | ( ) [ ] * + ? { } ,

以下字符必须转义： \ ^ 。$ | ( ) [ ] * + ? { } ,

http://psoug.org/snippet/Regular-Expressions--Regexp-Cheat-Sheet_856.htm

http://psoug.org/snippet/Regular-Expressions--Regexp-Cheat-Sheet_856.htm

SELECT REGEXP_REPLACE('some.string[with(special)reg-exp]characters', '([][)(}{.$*+?,|^\])', '\') "REGEXP_REPLACE" FROM dual;

回答by Manuel Romeiro

This can be done without any regular expression and escapes by instrfunction, that will return 0if no match and > 0if match.

这可以在没有任何正则表达式的情况下完成，并通过instr函数进行转义，如果不匹配则返回 0，如果匹配则返回> 0。

For this works, you should add the separator_at the begin and at the end of needle_and haystack_before check if the haystack_contains needle_.

对于这项工作，您应该在needle_和haystack_的开头和结尾添加separator_，然后再检查haystack_是否包含needle_。

Proof of concept

概念证明

select haystack_, needle_, separator_, instr(separator_||haystack_||separator_, separator_||needle_||separator_) result_, expected_
from (
    select 'john,eve,maria,steve' as haystack_ , 'eve' as needle_, ',' as separator_, '>0'as expected_ from dual union all
    select 'john,eve,maria,steve' as haystack_ , 'john' as needle_, ',' as separator_, '>0'as expected_ from dual union all
    select 'john,eve,maria,steve' as haystack_ , 'joh' as needle_, ',' as separator_, '=0'as expected_ from dual union all
    select 'john,eve,maria,steve' as haystack_ , 'steve' as needle_, ',' as separator_, '>0'as expected_ from dual union all
    select 'john,eve,maria,steve' as haystack_ , 'stev' as needle_, ',' as separator_, '=0'as expected_ from dual union all
    select 'john,eve,maria,steve' as haystack_ , 'teve' as needle_, ',' as separator_, '=0'as expected_ from dual union all
    select 'john.maria.steve' as haystack_ , 'eve' as needle_, '.' as separator_, '=0'as expected_ from dual union all
    select 'john_maria_steve' as haystack_ , 'eve' as needle_, '_' as separator_, '=0'as expected_ from dual union all
    select 'john%maria%steve' as haystack_ , 'eve' as needle_, '%' as separator_, '=0'as expected_ from dual
) t;

result:

结果：

HAYSTACK_            NEEDLE_ SEPARATOR_ RESULT_ EXPECTED_
john,eve,maria,steve eve     ,          6       >0
john,eve,maria,steve john    ,          1       >0
john,eve,maria,steve joh     ,          0       =0
john,eve,maria,steve steve   ,          16      >0
john,eve,maria,steve stev    ,          0       =0
john,eve,maria,steve teve    ,          0       =0
john.maria.steve     eve     .          0       =0
john_maria_steve     eve     _          0       =0
john%maria%steve     eve     %          0       =0

回答by tbone

Try this:

尝试这个：

CREATE OR REPLACE 
FUNCTION list_contains(needle_    IN VARCHAR2,
                       haystack_  IN VARCHAR2,
                       separator_ IN VARCHAR2 DEFAULT ',')
return number AS
  l_return_count number := 0;
BEGIN
  with haystack_ary as (
    select extractvalue(x.column_value, 'e') as val
    from xmltable ('e' passing xmlparse( content  '<e>' || replace(haystack_, separator_, '</e><e>') || '</e>')) x
  )
  select 
  --count(1)
  --return as a "bool"(1=true,0=false)
  decode(count(1), 0, 0, 1)
  into l_return_count
  from haystack_ary
  where lower(needle_) = lower(haystack_ary.val);

  return l_return_count;
END;

I made it case insensitive with lower function. If you wanted, you could also trim whitespace: lower(trim(needle_)) = lower(trim(haystack_ary.val))

我用较低的功能使它不区分大小写。如果需要，您还可以修剪空格： lower(trim(needle_)) = lower(trim(haystack_ary.val))