使用Oracle SQL检索字符串中的某些字符串 [英] Retrieving certain strings within a string using Oracle SQL

问题描述

我正在尝试使用Oracle SQL 11g R2进行以下字符串搜索:

I am trying the following string search using Oracle SQL 11g R2:

数据是:

| CN = aXYZApple-Au，OU =受管理，OU =群组，DC =核心，DC = dir，DC = abc，DC = com | CN = 31107427，OU =分发列表，OU =共享 邮箱，DC =核心，DC =目录，DC = abc，DC = com | CN = ea90045052，OU =组，OU = eProfile，DC =核心，DC =目录，DC = abc，DC = com | CN = S0901448，OU =分发列表，OU =共享 邮箱，DC =核心，DC =目录，DC = abc，DC = com | CN = 00900887，OU =分布 列表，OU =共享邮箱，DC =核心，DC = dir，DC = abc，DC = com | CN = NSMMMM，OU = LRP，OU = Groups，DC = core，DC = dir，DC = abc，DC = com | CN = aXYZApple只读，OU =托管，OU =组，DC =核心，DC = dir，DC = abc，DC = com | CN = WWSWW-Au，OU = LRP，OU = Groups，DC = core，DC = dir，DC = abc，DC = com | CN = aLogical_RW，OU =受管，OU =组，DC =核心，DC = dir，DC = abc，DC = com | CN = aXYZApple-Write，OU =受管理，OU =群组，DC =核心，DC = dir，DC = abc，DC = com |

| CN=aXYZApple-Au,OU=Managed,OU=Groups,DC=core,DC=dir,DC=abc,DC=com | CN=31107427,OU=Distribution Lists,OU=Shared Mailboxes,DC=core,DC=dir,DC=abc,DC=com | CN=ea90045052,OU=Groups,OU=eProfile,DC=core,DC=dir,DC=abc,DC=com | CN=S0901448,OU=Distribution Lists,OU=Shared Mailboxes,DC=core,DC=dir,DC=abc,DC=com | CN=00900887,OU=Distribution Lists,OU=Shared Mailboxes,DC=core,DC=dir,DC=abc,DC=com | CN=NSMMMM,OU=LRP,OU=Groups,DC=core,DC=dir,DC=abc,DC=com | CN=aXYZApple-Readonly,OU=Managed,OU=Groups,DC=core,DC=dir,DC=abc,DC=com | CN=WWSWW-Au,OU=LRP,OU=Groups,DC=core,DC=dir,DC=abc,DC=com | CN=aLogical_RW,OU=Managed,OU=Groups,DC=core,DC=dir,DC=abc,DC=com | CN=aXYZApple-Write,OU=Managed,OU=Groups,DC=core,DC=dir,DC=abc,DC=com |

从以上数据中，我需要获取仅包含字符串"aXYZApple"的所有字符串，并且将"OU = Managed"作为该字符串匹配的第二部分.

From the above data, I need to obtain all strings that hold the string "aXYZApple" only with the "OU=Managed" as the second part of this string match.

因此，根据以上内容，以下结果是我所追求的，并且只会显示:

So based on the above, the following results is what I am after and would be displayed only:

aXYZApple-Au
aXYZApple-Readonly
aXYZApple-Write

我正在使用Oracle regexp_like/regexp_replace.

Am using Oracle regexp_like/regexp_replace.

推荐答案

您可以使用

You could use regexp_substr to split your input data into lines and then look for the relevant strings, for example:

SQL> SELECT regexp_substr(line, 'aXYZApple[^,]*') subtxt
  2    FROM (SELECT regexp_substr(:x, '[^|]*\|', 1, rownum + 1) line
  3             FROM dual
  4           CONNECT BY LEVEL <= length(:x) - length(REPLACE(:x, '|', '')))
  5   WHERE regexp_substr(line || ',', '[^,]*,', 1, 2) = 'OU=Managed,'
  6     AND line LIKE '%aXYZApple%';

SUBTXT
--------------------------------------------------------------------------------
aXYZApple-Au
aXYZApple-Readonly
aXYZApple-Write

---

这里有一点解释.您必须逐步进行查询.

---

Here's a little explanation. You have to go through the query step by step.

查询的内部将遍历您的数据(对于每个|):

The inner part of the query will loop through your data (for each |):

SQL> SELECT regexp_substr(:x, '[^|]*\|', 1, rownum + 1) line
  2    FROM dual
  3  CONNECT BY LEVEL <= length(:x) - length(REPLACE(:x, '|', ''));

LINE
--------------------------------------------------------------------------------
 CN=aXYZApple-Au,OU=Managed,OU=Groups,DC=core,DC=dir,DC=abc,DC=com |
 CN=31107427,OU=Distribution Lists,OU=Shared Mailboxes,DC=core,DC=dir,DC=abc,DC=
 CN=ea90045052,OU=Groups,OU=eProfile,DC=core,DC=dir,DC=abc,DC=com |
 CN=S0901448,OU=Distribution Lists,OU=Shared Mailboxes,DC=core,DC=dir,DC=abc,DC=
 CN=00900887,OU=Distribution Lists,OU=Shared Mailboxes,DC=core,DC=dir,DC=abc,DC=
 CN=NSMMMM,OU=LRP,OU=Groups,DC=core,DC=dir,DC=abc,DC=com |
 CN=aXYZApple-Readonly,OU=Managed,OU=Groups,DC=core,DC=dir,DC=abc,DC=com |
 CN=WWSWW-Au,OU=LRP,OU=Groups,DC=core,DC=dir,DC=abc,DC=com |
 CN=aLogical_RW,OU=Managed,OU=Groups,DC=core,DC=dir,DC=abc,DC=com |
 CN=aXYZApple-Write,OU=Managed,OU=Groups,DC=core,DC=dir,DC=abc,DC=com |

然后您将在第二个位置循环搜索OU=Managed字符串:

You would then loop for the OU=Managed string in the second position:

SQL> SELECT regexp_substr(line || ',', '[^,]*,', 1, 2) second_part
  2    FROM (SELECT regexp_substr(:x, '[^|]*\|', 1, rownum + 1) line
  3             FROM dual
  4           CONNECT BY LEVEL <= length(:x) - length(REPLACE(:x, '|', '')));

SECOND_PART
--------------------------------------------------------------------------------
OU=Managed,
OU=Distribution Lists,
OU=Groups,
OU=Distribution Lists,
OU=Distribution Lists,
OU=LRP,
OU=Managed,
OU=LRP,
OU=Managed,
OU=Managed,

最后，选择最后一个regexp_substr的相关部分.

Finally, select the relevant part with a last regexp_substr.

这篇关于使用Oracle SQL检索字符串中的某些字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！