有趣的字符串转换

jackywood · 发表于 2006-3-31 16:51

要求用pl/sql写一个函数, 实现根据分割符把原字符串分成若干个字符串功能.

输入: string(字符串) 和 Delimiter (分隔符)
输出: substr1, ..., substrn (根据分割后的字符串排序, 不是子串在原字符串中的顺序)

[2006-4-4]
本贴中大家所写的函数的使用示例, 参见 "求最有效的实现方法" 第二页
http://www.itpub.net/showthread. ... 10&pagenumber=2
[2006-4-4]

示例:
[php]

1. 分割符为空, 返回字符串本身
SELECT * FROM ConvertStringToTable('a,b,c','');

strValue
----------------------------------------------------------------------------------------------------------------
a,b,c

(1 row(s) affected)

2. 根据分割符 "," 把字符串 'a,b,c' 分成 a b c 三个子字符串(不含分隔符)
SELECT * FROM ConvertStringToTable('a,b,c',',');

strValue
----------------------------------------------------------------------------------------------------------------
a
b
c

(3 row(s) affected)

3. 同2, 字符串中包含的子串内容相同, 显示结果按照子串进行排列, 而不是子串在原字符串中的位置排序
SELECT * FROM ConvertStringToTable('c,b,a',',');

strValue
----------------------------------------------------------------------------------------------------------------
a
b
c

(3 row(s) affected)

4. 包含的子串中如果有相同的子串, 需要报异常错误, 下面是MS SQL SERVER 中的错误示例, PL/SQL 中可以根据情况自定义错误信息
SELECT * FROM ConvertStringToTable('c,b,c',',');

Server: Msg 2627, Level 14, State 1, Procedure ConvertStringToTable, Line 26
Violation of PRIMARY KEY constraint 'PK__@tblList__6EF57B66'. Cannot insert duplicate key in object '#6E01572D'.
The statement has been terminated.

5. 如果分割符为多个字符组成, 仅以第一个字符作为有效分割符, 其他忽略
SELECT * FROM ConvertStringToTable('c,b,a',',d');

strValue
----------------------------------------------------------------------------------------------------------------
a
b
c

(3 row(s) affected)

6. 如果字符串结尾出现分割符, 那么分割符后的认为是空子字符串, 也要作为结果返回, 返回的排序要按照 nulls first 的方式处理
SELECT * FROM ConvertStringToTable('c,b,a,',',')

strValue
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

a
b
c

(4 row(s) affected)

-------------
[/php]

示例中提到的这6点也是对函数的要求, 有兴趣的朋友可以做做
不过要说明, 这个不是什么考试题或是面试题, 是数据库移植中对SQL SERVER中自定义函数的移植

如果对题目还有什么疑问, 随时可以提出

Laurence.li · 发表于 2006-3-31 18:02

没时间做，帮你置顶吧，直到得到好的答案

MountLion · 发表于 2006-3-31 19:09

首先，要保证排序只能通过order by，这一点楼主想必是知道的：）

[php]
create or replace type t_vc is table of varchar2(100);  --要不怕占内存，也可以整大点

create or replace function myconvert( p_str varchar2, p_delim varchar2) return t_vc as
  v_result t_vc;
  v_delimlen number := length(p_delim);
  i       number := 1;
  j       number;
  p       integer; --pointer to string
  p2       integer; --pointer to delim
begin
  if p_str is null then
return null;
  elsif p_delim is null then
return t_vc(p_str);
  end if;

  v_result := t_vc();
  p := 1;
  loop
p2 := instr(p_str, p_delim, p);
v_result.extend;
if p2 <> 0 then
   v_result(i) := substr(p_str, p, p2 - p);
else
   v_result(i) := substr(p_str, p);
   exit;
end if;

-- check duplication value
for j in 1..i loop
   if j<>i and v_result(j)=v_result(i) then
      raise_application_error(-20000, 'Duplicate value on '||j||' and '||i||' :'||v_result(i));
   end if;
end loop;

p := p2 + v_delimlen;
i := i + 1;
  end loop;

  return v_result;
end;

--可以使用了：
select column_value from table(cast(myconvert('b,,c,a,,', ',') as t_vc))  order by 1 nulls first;

COLUMN_VALUE
------------

a
b
c
(6 Rows)

--
[/php]

MountLion · 发表于 2006-3-31 19:11

[php]
select column_value from table(cast(myconvert('aaa分隔符bb分隔符combo分隔符hello', '分隔符') as t_vc)) order by 1 nulls first;

COLUMN_VALUE
aaa
bb
combo
hello
----
[/php]

hotiice · 发表于 2006-3-31 22:58

[php]
判断重复子串用了顺序查找，当要分裂的字符串很长时，速度很慢，用下面的例子测试
set echo on

alter system flush shared_pool;

set timing on
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
c char;
sz1 varchar(32767);
begin
c:=chr(39);
for i in 1 .. 100
loop
sz1:=sz1||','||i;
      open l_rc for
   'select column_value from table(cast(myconvert('
||c||'b,,c,a,,'||sz1||c||', '||c||','||c||') as t_vc))  order by 1 nulls first';
      fetch l_rc into l_dummy;
      close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
   ' seconds...' );
end;
/
SQL> set echo on
SQL>
SQL> alter system flush shared_pool;

系统已更改。

已用时间:  00: 00: 00.00
SQL>
SQL> set timing on
SQL> declare
  2    type rc is ref cursor;
  3    l_rc rc;
  4    l_dummy all_objects.object_name%type;
  5    l_start number default dbms_utility.get_time;
  6    c char;
  7    sz1 varchar(32767);
  8  begin
  9  c:=chr(39);
10    for i in 1 .. 1000
11    loop
12  sz1:=sz1||','||i;
13       open l_rc for
14       'select column_value from table(cast(myconvert('
15  ||c||'b,,c,a,,'||sz1||c||', '||c||','||c||') as t_vc))  order by 1 nulls first';
16       fetch l_rc into l_dummy;
17       close l_rc;
18    end loop;
19    dbms_output.put_line
20    ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
21       ' seconds...' );
22  end;
23  /

PL/SQL 过程已成功完成。

已用时间:  00: 04: 24.09

SQL> 15
15* ||c||'b,,c,a,,'||sz1||c||', '||c||','||c||') as t_vc))  order by 1 nulls first';
SQL> c/sz1/i
15* ||c||'b,,c,a,,'||i||c||', '||c||','||c||') as t_vc))  order by 1 nulls first';
SQL> /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 04.06
SQL> 15
15* ||c||'b,,c,a,,'||i||c||', '||c||','||c||') as t_vc))  order by 1 nulls first';
SQL> c/i/length(sz1)
15* ||c||'b,,c,a,,'||length(sz1)||c||', '||c||','||c||') as t_vc))  order by 1 nulls first';
SQL> /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 03.04

[/php]

lastwinner · 发表于 2006-4-1 02:47

简单的拆分可以这么做
[php]
select c2 from test;

C2
----------------------------------------
a,b,c,dd,efg
h,iii

select substr(c2,st+1,en-st-1) what_you_want from (select c2, decode(rn,1,0,instr(c2,',',1,rn-1)) st , decode(instr(c2,',',1,rn),0,length(c2)+1, instr(c2,',',1,rn)) en from (select distinct c2, level rn from test connect by level<=length(c2)-length(replace(c2,',',''))+1))
/

.........................
[/php]

如果c2中不包含分隔符，那就直接返回c2
如果字符串前后都出现分隔符（全包围），那么会选择出两条NULL
如果分隔符是左包围或者右包围，那么就会选择出一条NULL，若为左包围则NULL出现在第一条记录，若为右包围则NULL出现在最后一条记录

当然，这个只能完全满足jacky的第二点要求，第三、六点算满足了一半

zuochc · 发表于 2006-4-1 14:01

jackywood兄出了题目, 支持一下, 也算是抛砖引玉吧!

[php]
______________________________________________________________
SQL> create or replace function strtotbl(str in varchar2, sp in varchar2)
  2    return myTableType pipelined
  3  is
  4 ch varchar2(1) := substr(sp, 1, 1);
  5 n pls_integer:= length(str) - length(replace(str, ch)) + 1;
  6 last_str varchar2(100) := '';
  7  begin
  8 if n > 1 then
  9    for rec in (select substr(str, instr(ch||str, ch, 1, rownum),
10                      instr(str||ch, ch, 1, rownum) - instr(ch||str, ch, 1, rownum)) str
11          from dual
12          connect by rownum <= n
13          order by str nulls first) loop
14       if last_str = rec.str then
15          raise_application_error(-20101, 'Substr is dup!');
16       else
17          last_str := rec.str;
18          pipe row(last_str);
19       end if;
20    end loop;
21 else
22       pipe row(str);
23 end if;
24 return;
25  end;
26  /

函数已创建。

SQL> select * from table(strtotbl('a,b,c', ''));

COLUMN_VALUE
------------------------------
a,b,c

SQL> select * from table(strtotbl('a,b,c', ',b'));

COLUMN_VALUE
------------------------------
a
b
c

SQL> select * from table(strtotbl('c,b,a', ','));

COLUMN_VALUE
------------------------------
a
b
c

SQL> select * from table(strtotbl('c,b,a,', ','));

COLUMN_VALUE
------------------------------

a
b
c

SQL> select * from table(strtotbl('c,b,c,', ','));
ERROR:
ORA-20101: Substr is dup!
ORA-06512: 在 "ZCC.STRTOTBL", line 15

未选定行

_____________________________________________________________
[/php]

刚从JACKYWOOD兄处学来, 修改一下格式试试啊.

MountLion · 发表于 2006-4-2 12:27

高版本就是好，817好象不支持pipeline。

hotiice ：
  我想根据楼主的实际需求，分段不会太多，10个8个元素的话，用顺序查找就够了。

  zouchc：
  使用sql，又多了sql engine和 plsql engine之间的交互，怕是会得不偿失。
  （你是不是zzc ？）

zuochc · 发表于 2006-4-2 17:43

最初由 MountLion 发布
[B]高版本就是好，817好象不支持pipeline。

hotiice ：
  我想根据楼主的实际需求，分段不会太多，10个8个元素的话，用顺序查找就够了。

  zouchc：
  使用sql，又多了sql engine和 plsql engine之间的交互，怕是会得不偿失。
  （你是不是zzc ？） [/B]

你好, ZouChC就是ZCC啊.

在8.17中可以也可以返回TABLE结构的. 我所以用SQL实现,
1) 楼主要求的排序, 若在函数中实现, 需要写比较多的代码.
2) 用一个SQL可以做较多的事, 并且实现的排序效率也很难说啊, 毕竟PL/SQL不是做数值处理的.

这里有一个早先从书上看来的函数, 817下可以用. 贴出来供朋友参考:

[php]

--建用户类
CREATE TYPE my_tbl_type AS OBJECT(engineid VARCHAR2(20));

CREATE OR REPLACE TYPE myTableType as TABLE OF my_tbl_type;

--将字付串转化为表
CREATE OR REPLACE FUNCTION str2tbl(p_str IN VARCHAR2) RETURN myTableType
AS
l_str VARCHAR2(1000):=p_str||',';
l_n NUMBER;
l_data myTableType:=myTableType();
BEGIN
LOOP
l_n:=instr(l_str,',');
      EXIT WHEN (NVL(l_n,0)=0);
l_data.extend;
      l_data(l_data.count):=my_tbl_type(SUBSTR(l_str,1,l_n-1));
      l_str:=SUBSTR(l_str,l_n+1);
END LOOP;
RETURN l_data;
END;

--示例应用
var aaa varchar2(1000)
exec :aaa:='04101025765,04101027465,04101027485,04101027695,04101027745'
select * from table(cast(str2tbl(:aaa) as myTableType));

COLUMN_VALUE
------------------------------
04101025765
04101027465
04101027485
04101027695
04101027745
[/php]

www.wwf.co · 发表于 2006-4-3 10:50

最初由 MountLion 发布
[B]首先，要保证排序只能通过order by，这一点楼主想必是知道的：）

[php]
create or replace type t_vc is table of varchar2(100);  --要不怕占内存，也可以整大点

create or replace function myconvert( p_str varchar2, p_delim varchar2) return t_vc as
  v_result t_vc;
  v_delimlen number := length(p_delim);
  i       number := 1;
  j       number;
  p       integer; --pointer to string
  p2       integer; --pointer to delim
begin
  if p_str is null then
return null;
  elsif p_delim is null then
return t_vc(p_str);
  end if;

  v_result := t_vc();
  p := 1;
  loop
p2 := instr(p_str, p_delim, p);
v_result.extend;
if p2 <> 0 then
   v_result(i) := substr(p_str, p, p2 - p);
else
   v_result(i) := substr(p_str, p);
   exit;
end if;

-- check duplication value
for j in 1..i loop
   if j<>i and v_result(j)=v_result(i) then
      raise_application_error(-20000, 'Duplicate value on '||j||' and '||i||' :'||v_result(i));
   end if;
end loop;

p := p2 + v_delimlen;
i := i + 1;
  end loop;

  return v_result;
end;

--可以使用了：
select column_value from table(cast(myconvert('b,,c,a,,', ',') as t_vc))  order by 1 nulls first;

COLUMN_VALUE
------------

a
b
c
(6 Rows)

--
[/php] [/B]

SQL> select column_value from table(cast(myconvert('b,c,b', ',') as t_vc))
2 order by 1 nulls first;

COLUMN_VALUE
------------------------------------------------------------------------------

b
b
c

为什么不报错?

[精华] 有趣的字符串转换

浏览过的版块