按字母顺序在varchar2中对字符进行排序
从http://forums.oracle.com/forums/thread.jspa?messageID=1791550这可能工作,但不拥有10g来测试的答案...
SELECT MIN(permutations)
FROM (SELECT REPLACE (SYS_CONNECT_BY_PATH (n, ','), ',') permutations
FROM (SELECT LEVEL l, SUBSTR ('&col', LEVEL, 1) n
FROM DUAL
CONNECT BY LEVEL <= LENGTH ('&col')) yourtable
CONNECT BY NOCYCLE l != PRIOR l)
WHERE LENGTH (permutations) = LENGTH ('&col')
在这个例子中col
是在SQL * Plus定义的,但如果你把这个函数你可以传入它,或者可以直接将它改写成表格列。
我会以此为出发点而不是解决方案;最初的问题是关于anagrams,所以它被设计为查找所有的排列,所以类似但简化的东西可能是可能的。我怀疑这对于大数值并不能很好地扩展。
假设你不介意的人物每行返回1:
select substr(str, r, 1) X from (
select 'CAB' str,
rownum r
from dual connect by level <= 4000
) where r <= length(str) order by X;
X
=
A
B
C
你应该记住,没有共同的协议是什么“字母”的意思。这完全取决于它是哪个国家,谁在查看你的数据以及它处于什么背景。
例如在DK中,有大量不同的a,a,b,c, ,O,A
- 每字母表:A,AA,b,C,æ,O,A
- 一些字典:A,AA,A,b,C,æ,ø
- 对于其他字典:a,b,c,æ,ø,aa,
- 按照Microsoft标准:a,b,c,æ,ø,aa,å
查看更多信息http://www.siao2.com/2006/04/27/584439.aspx。这也是碰巧成为这些问题的好博客。
因此,最终我去了PL/SQL路由,因为经过一段时间的搜索后,我意识到没有可以使用的内置函数。
这是我想出来的。它基于关联数组的未来,即Oracle按照排序顺序保存键。
create or replace function sort_chars(p_string in varchar2) return varchar deterministic
as
rv varchar2(4000);
ch varchar2(1);
type vcArray is table of varchar(4000) index by varchar2(1);
sorted vcArray;
key varchar2(1);
begin
for i in 1 .. length(p_string)
loop
ch := substr(p_string, i, 1);
if (sorted.exists(ch))
then
sorted(ch) := sorted(ch) || ch;
else
sorted(ch) := ch;
end if;
end loop;
rv := '';
key := sorted.FIRST;
WHILE key IS NOT NULL LOOP
rv := rv || sorted(key);
key := sorted.NEXT(key);
END LOOP;
return rv;
end;
简单的性能测试:
set timing on;
create table test_sort_fn as
select t1.object_name || rownum as test from user_objects t1, user_objects t2;
select count(distinct test) from test_sort_fn;
select count (*) from (select sort_chars(test) from test_sort_fn);
Table created.
Elapsed: 00:00:01.32
COUNT(DISTINCTTEST)
-------------------
384400
1 row selected.
Elapsed: 00:00:00.57
COUNT(*)
----------
384400
1 row selected.
Elapsed: 00:00:00.06
您可以使用下面的查询:
select listagg(letter)
within group (order by UPPER(letter), ASCII(letter) DESC)
from
(
select regexp_substr('gfedcbaGFEDCBA', '.', level) as letter from dual
connect by regexp_substr('gfedcbaGFEDCBA', '.', level) is not null
);
子查询拆分使用REGEXP_SUBSTR串入记录(每一个单个字符),和外部查询将记录合并为一个字符串,使用listagg,排序后。
这里您应该小心,因为按照Cine指出,按字母排序取决于数据库配置。
在上面的例子中,字母按“按字母顺序排列”并按ascii代码降序排列,在我的情况下,结果为“aAbBcCdDeEfFgG”。 您的情况可能会有所不同。
您也可以使用nlssort对信件进行排序 - 它可以更好地控制排序顺序,因为您可以独立于数据库配置。
select listagg(letter)
within group (order by nlssort(letter, 'nls_sort=german')
from
(
select regexp_substr('gfedcbaGFEDCBA', '.', level) as letter from dual
connect by regexp_substr('gfedcbaGFEDCBA', '.', level) is not null
);
上面的查询会给你也是“aAbBcCdDeEfFgG”,但如果你改变了“德国”,以“西班牙”,你会得到“AaBbCcDdEeFfGg”代替。
所以如果你的列值是 选择'CAB'从双 它会返回'ABC'? – EvilTeach 2010-05-14 16:12:06
这是正确的。 – mtim 2010-05-14 16:15:50