Oracle-字符串组合排列 [英] Oracle - string combinatorial permutation
问题描述
我认为我有一个复杂的要求.
I think I have a complex requirement.
这是使用Oracle 10.2的组合排列,我能够使用笛卡尔联接来解决它,但是我认为它需要一些改进以使其更简单,更灵活.
It's a combinatorial permutation using Oracle 10.2, I'was able to solve it using cartesian joins, but I think that it need some improvements to made it simplest and more flexible.
主要行为.
输入字符串:一二"
输出: '一' '二' '一二' 两个一个"
output: 'one' 'two' 'one two' 'two one'
对于我的解决方案,我将字符串数限制为5(请注意,输出是阶乘附近的数字)
For my solution I've restricted the number of strings to 5 (note that the output is a number near the factorial)
SQL:
with My_Input_String as (select 1 as str_id, 'alpha beta omega gama' as str from dual )
--------logic-------
, String_Parse as (
SELECT REGEXP_SUBSTR(str, '[^ ]+', 1, ROWNUM) str
FROM My_Input_String
where rownum < 6 -- string limitation --
CONNECT BY level <= LENGTH(REGEXP_REPLACE(str, '([^ ])+|.', '\1') )
)
--------CRAP select need refactoring-------
select str from String_Parse
union
select REGEXP_REPLACE(trim(s1.str||' '||s2.str||' '||s3.str||' '||s4.str||' '||s5.str), '( ){2,}', ' ') as str
from
(select str from String_Parse union select ' ' from dual) s1,
(select str from String_Parse union select ' ' from dual) s2,
(select str from String_Parse union select ' ' from dual) s3,
(select str from String_Parse union select ' ' from dual) s4,
(select str from String_Parse union select ' ' from dual) s5
where
--
s1.str <> s2.str and s1.str <> s3.str and s1.str <> s4.str and s1.str <> s5.str
--
and s2.str <> s3.str and s2.str <> s4.str and s2.str <> s5.str
--
and s3.str <> s4.str and s3.str <> s5.str
--
and s4.str <> s5.str
推荐答案
通用的.最终真的很简单(但是花了我一段时间才到达那里)
Got the generic one. Really simple in the end (but took me a while to get there)
WITH words AS
( SELECT REGEXP_SUBSTR( '&txt', '\S+', 1, LEVEL ) AS word
, LEVEL AS num
FROM DUAL
CONNECT BY LEVEL <= LENGTH( REGEXP_REPLACE( '&txt', '\S+\s*', 'X' ) )
)
SELECT SYS_CONNECT_BY_PATH( W.word, ' ' )
FROM words W
CONNECT BY NOCYCLE PRIOR W.num != W.num
Edit2:删除了多余的maxnum内容.以前尝试遗留下来的东西
Removed redundant maxnum stuff. Left over from previous attempts
这篇关于Oracle-字符串组合排列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!