关于oracle字符串汇总

-- sample data
drop table t_collect purge;
create table t_collect as
  mod(rownum,30) as flag, lpad(dbms_random.string('l',3),4,' ') as val
  from dual connect by rownum

collect函数(Oracle10g)
-- 1: use collect funtion
select flag,
       my_tk.f_list2str(cast(collect(trim(val)) as my_tk_str_tab_type)) as ename
  from t_collect sample(10)
 group by flag
 order by 1;

sys_connect_by_path
-- 2: use sys_connect_by_path and row_number function
select t1.flag, substr(max(sys_connect_by_path(t1.val, ',')), 2) q
  from (select a.flag,
               trim(a.val) as val,
               row_number() over(partition by a.flag order by a.val) rn
          from t_collect sample(10) a) t1
 start with t1.rn = 1
connect by t1.flag = prior t1.flag
       and t1.rn - 1 = prior t1.rn
 group by t1.flag
 order by 1;

user-defined-function
-- 3: use user-defined-function
select flag,
       string_agg(trim(val)) as ename
  from t_collect sample(10)
 group by flag
 order by 1; 

create or replace type my_tk_str_tab_type is table of varchar2(100);

Perl学习手札 chm版

Perl学习手札是台湾perl高手写的一篇文章，特打包为chm版，方便大家阅读。 关于本书 1. 关于Perl 1.1 Perl的历史 1.2 Perl的概念 1.3 特色 1.4 使用Perl的环境 1.5 开始使用 Perl 1.6 你的第一个Perl程序 2. 标量变量(Scalar) 2.1 关于标量 2.1.1 数值 2.1.2 字符串 2.1.3 数字与字符串转换 2.2 使用你自己的变量 2.3 赋值 2.3.1 直接设定 2.3.2 还可以这样 2.4 运算 2.5 变量的输出/输入 2.

下载

  ---------------------------------------------------------------------
  function f_list2str
  (
    p_list      my_tk_str_tab_type,
    p_separator varchar2 default ',',
    p_sort      integer default 1
  ) return varchar2 is
    l_idx  pls_integer := 0;
    l_str  varchar2(32767) := null;
    l_spt  varchar2(10) := null;
    l_list my_tk_str_tab_type := p_list;
  begin
    if p_sort = 1 then
      l_list := f_sort_list(p_list);
    end if;
 
    l_idx := l_list.first;
    while l_idx is not null loop
      l_str := l_str || l_spt || l_list(l_idx);
      l_spt := p_separator;
      l_idx := l_list.next(l_idx);
    end loop;
 
    return l_str;
  end;

-- user-defined-function
CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/