regex_substr中的by by子句连接 [英] connect by clause in regex_substr

问题描述

我无法理解该声明-谷歌搜索

I cant get the understanding of this statement - not eveN after googling around

 
pv_no_list :='23,34,45,56';
SELECT   DISTINCT REGEXP_SUBSTR (pv_no_list,
                                                     '[^,]+',
                                                     1,
                                                     LEVEL)
                                         no_list
                      FROM   DUAL
                CONNECT BY   REGEXP_SUBSTR (pv_no_list,
                                            '[^,]+',
                                            1,
                                            LEVEL) IS NOT NULL

推荐答案

connected by的滥用"(正如Colin't Hart所说的)在这里具有良好的用途: 通过使用REGEXP_SUBSTR，您只能提取4个匹配项(23、34、45、56)之一:regex [^,]+匹配字符串中不包含逗号的任何字符序列.

The "abuse" (as Colin 't Hart put it) of connected by has a good purpose here: by using REGEXP_SUBSTR you can extract only one of the 4 matches (23,34,45,56): the regex [^,]+ matches any character sequence in the string which does not contain a comma.

如果您尝试运行:

SELECT REGEXP_SUBSTR ('23,34,45,56','[^,]+') as "token" 
FROM   DUAL

您将获得23.

，如果您尝试运行:

SELECT REGEXP_SUBSTR ('23,34,45,56','[^,]+',1,1) as "token"
FROM   DUAL

您还将获得23，因为现在我们还设置了 两个附加参数 :从位置1开始查找(这是默认设置)，并返回第一个出现的位置.

you'll also get 23 only that now we also set two additional parameters: start looking in position 1 (which is the default), and return the 1st occurrence.

现在运行:

SELECT REGEXP_SUBSTR ('23,34,45,56','[^,]+',1,2) as "token"
FROM   DUAL

这一次，我们将得到34(第二次出现)，并使用3作为最后一个参数将返回45，依此类推.

this time we'll get 34 (2nd occurrence) and using 3 as the last parameter will return 45 and so on.

将递归connected by与level结合使用可确保您将收到所有相关结果(尽管不一定按原始顺序！):

The use of recursive connected by along with level makes sure you'll receive all the relevant results (not necessarily in the original order though!):

SELECT DISTINCT REGEXP_SUBSTR ('23,34,45,56','[^,]+',1,LEVEL) as "token"
FROM   DUAL
CONNECT BY REGEXP_SUBSTR ('23,34,45,56','[^,]+',1,LEVEL) IS NOT NULL
order by 1

将返回:

TOKEN
23
34
45
56

不仅包含所有4个结果，而且还将其分成结果集中的单独行！

which not only contains all 4 results, but also breaks it into separate rows in the resultset!

如果您愿意 小提琴 -它可能会让您对主题有更清晰的了解.

If you'll fiddle with it - it might give you a clearer view of the subject.

这篇关于regex_substr中的by by子句连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！