Oracle: Oracle 8i中REGEXP_LIKE函数的替代

I have a SQL that uses REGEXP_LIKE function in the where clause, now i need the equivalent of that function which will run in Oracle 8i.

我有一个在where子句中使用REGEXP_LIKE函数的SQL，现在我需要一个在Oracle 8i中运行的等价函数。

The regex look like this:

regex如下所示:

where REGEXP_LIKE(parm, '^[PMF][[:digit:]]+[_].*')

Thanks in advance.

提前谢谢。

3 个解决方案

#1

You could try

你可以试试

WHERE SUBSTR(parm,1,1) IN ('P','M','F')
AND substr(parm,2,1) between '0' and '9'
AND substr(ltrim(substr(parm,2),'0123456789'),1,1) = '_'

First character is P, M or F. Second character is a digit Second character onwards, stripping all the digits from the left, should start with underscore

第一个字符是P、M或f。第二个字符是一个数字第二个字符，从左边去掉所有的数字，应该以下划线开头

PS. Please shoot your 8i database. Not only is 8i out of support, 9i and 10g are also out of support (or at least they are in the 'degraded, please stop using them' level of support).

请拍下你的8i数据库。不仅8i不支持，9i和10g也不支持(或者至少它们处于“降级，请停止使用它们”级别的支持)。

#2

In googling around, it seems there's a package called OWA_PATTERN that was available back as far as 8i. It provided quite a bit of regular expression support. It was apparently part of the PL/SQL Web Toolkit installation. It may or may not be available in your 8i installation. Check with the DBA for the database.

在google上搜索时，似乎有一个名为OWA_PATTERN的软件包，可以追溯到8i。它提供了相当多的正则表达式支持。它显然是PL/SQL Web Toolkit安装的一部分。在你的8i安装中可能有也可能没有。与DBA检查数据库。

Here is a 9i document that references it.

这里有一个引用它的9i文档。

#3

Unfortunately, prior to REGEXP_LIKE things were rather grim. We had to do this by hand, in the pouring rain, both ways. Something like this is probably necessary:

不幸的是，在REGEXP_LIKE出现之前，情况相当严峻。我们必须手工做这件事，在倾盆大雨中。类似这样的事情可能是必要的:

WHERE SUBSTR(parm,1,1) IN ('P','M','F')
AND digits_followed_by_underscore(SUBSTR(parm,2)) = 'Y'

Using the following procedure (warning: hastily hacked together, not tested):

使用以下程序(警告:仓促被黑，未测试):

CREATE PROCEDURE digits_followed_by_underscore(v IN VARCHAR2)
  RETURN VARCHAR2 IS
  digit_found VARCHAR2(1) := 'N';
BEGIN
  IF LENGTH(v) = 0 THEN
    RETURN 'N';
  END IF;
  FOR i IN 1..LENGTH(v) LOOP
    IF SUBSTR(v,i,1) BETWEEN '0' AND '9' THEN
      digit_found := 'Y';
    ELSIF SUBSTR(v,i,1) = '_' THEN
      RETURN digit_found;
    ELSE
      RETURN 'N';
    END IF;
  END LOOP;
  RETURN 'N';
END;

#1