在varchar2()中查找所有匹配项 [英] Find all matches in a varchar2()

问题描述

我有一列是varchar2，其中包含如下数据:....[A1]...[A2]... 我想取出由[]封装的所有数据，这些数据以字母开头，可以有一个或两个数字，即:[B1]，[B23].

I've a column that is a varchar2 that contains data like: ....[A1]...[A2]... I want to take out all data that is encapsulated by [] that starts with an alphabetic letter and can have either one or two number, i.e: [B1], [B23].

所以我想要这样的东西:"WHERE列类似于'[__]' OR column is like '[___]'，但_不仅是字母或数字之外的任何值.

So I want something like: "WHERE column is like '[__]' OR column is like '[___]' but _ isnt just any value but alphabetic or numeric.

是否可以以某种方式将它们存储以备后用?

And is it possible to store them in someway for later use?

推荐答案

好问题！ 这是一个小提琴，其中显示了如何将匹配项查询到结果集中.

Great question! Here's a Fiddle showing how to query the matches into a result set.

这是冗长的解释，以防小提琴中的查询没有意义:)

And here's the long explanation in case the query in the Fiddle doesn't make sense :)

我正在使用名为RegEx_Test的表以及列MyVal的表.这是表格的内容:

I'm using a table named RegEx_Test with a column MyVal. Here's the table's contents:

MyVal
------------------------------
[A1][abc][B23][D123]a33[bx5]
[Z15][ax0][B0][F13]R3
[X215][A3A][J99]F33F33G24[43][R3]
[Z99][c1][F3][d33]3x24[Y3][f13]
[9a][D41][Q39][XX12]B27[T03][J12]

您的正则表达式始终是:\[[[:alpha:]][[:digit:]]{1,2}\].除了使用POSIX :alpha:和:digit:指示器(在使用国际字符集的情况下更安全)之外，其他答案均与其他答案相同.

Your regexp throughout is this: \[[[:alpha:]][[:digit:]]{1,2}\]. It's the same as in the other answer except with the POSIX :alpha: and :digit: indicators, which are safer in case of international charactersets.

首先，您需要知道任何一行上的最大匹配数.为此使用REGEXP_COUNT

First, you need to know the maximum number of matches on any line. Use REGEXP_COUNT for this:

SELECT MAX(REGEXP_COUNT(MyVal, '\[[[:alpha:]][[:digit:]]{1,2}\]'))
  FROM Regex_Test

MAX(REGEXP_COUNT(My...
----------------------
                     6

使用该最大计数获得一个计数器"表(即下面的SELECT ... FROM DUAL)，并将计数器表与查询交叉连接，该查询将使用REGEXP_SUBSTR拉取您的值. REGEXP_SUBSTR有一个出现"参数，它将使用Counter:

Use that maximum count to get a "counter" table (that's the SELECT ... FROM DUAL below) and cross-join the counter table with a query that will pull your values using REGEXP_SUBSTR. REGEXP_SUBSTR has an "occurrence" parameter and that will use the Counter:

SELECT
  MyVal,
  Counter,
  REGEXP_SUBSTR(MyVal, '\[[[:alpha:]][[:digit:]]{1,2}\]', 1, Counter) Matched
FROM Regex_Test
CROSS JOIN (
   SELECT LEVEL Counter
   FROM DUAL
   CONNECT BY LEVEL <= (
     SELECT MAX(REGEXP_COUNT(MyVal, '\[[[:alpha:]][[:digit:]]{1,2}\]'))
     FROM Regex_Test)) Counters

以下是针对我的表格运行的示例(部分结果):

Here's a sample run against my table (partial results):

MyVal                              Counter Matched
---------------------------------- ------- -------
[9a][D41][Q39][XX12]B27[T03][J12]        1 [D41]
[9a][D41][Q39][XX12]B27[T03][J12]        2 [Q39]
[9a][D41][Q39][XX12]B27[T03][J12]        3 [T03]
[9a][D41][Q39][XX12]B27[T03][J12]        4 [J12]
[9a][D41][Q39][XX12]B27[T03][J12]        5
[9a][D41][Q39][XX12]B27[T03][J12]        6
[A1][abc][B23][D123]a33[bx5]             1 [A1]
[A1][abc][B23][D123]a33[bx5]             2 [B23]
[A1][abc][B23][D123]a33[bx5]             3
... and so on - total is 30 rows

这时，您将获得单个匹配项的结果集，以及一行中小于最大匹配项的null.比赛仍然有他们的括号.用一个外部查询将整个内容括起来，该外部查询将过滤掉null并删除方括号，您便拥有了最终列表:

At this point you have a result set of individual matches, plus nulls where a row had less than the maximum matches. The matches still have their surrounding brackets. Surround the whole thing with an outer query that will filter out the nulls and remove the brackets, and you have your final list:

SELECT SUBSTR(Matched, 2, LENGTH(Matched)-2) FROM (
  SELECT
    MyVal,
    Counter,
    REGEXP_SUBSTR(MyVal, '\[[[:alpha:]][[:digit:]]{1,2}\]', 1, Counter) Matched
  FROM Regex_Test
  CROSS JOIN (
     SELECT LEVEL Counter
     FROM DUAL
     CONNECT BY LEVEL <= (
       SELECT MAX(REGEXP_COUNT(MyVal, '\[[[:alpha:]][[:digit:]]{1,2}\]'))
       FROM Regex_Test)) Counters
) WHERE Matched IS NOT NULL

这是小提琴上的查询，可以在其他查​​询中使用.

This is the query that's on the Fiddle, and it can be used in another query.

这篇关于在varchar2()中查找所有匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！