Wednesday, May 18, 2011
Sunday, May 8, 2011
Oracle: Function to concatenate column output
Type:
create or replace
TYPE "STRINGAGGTYPE" as object
(
theString varchar2(4000),
static function
ODCIAggregateInitialize(sctx IN OUT StringAggType )
return number,
member function
ODCIAggregateIterate(self IN OUT StringAggType ,
value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN StringAggType,
returnValue OUT varchar2,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT StringAggType,
ctx2 IN StringAggType)
return number
);
Function:
create or replaceNow, you can run a query like this:
FUNCTION stringAgg(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING StringAggType;
select deptno, stringAgg(ename) enames from emp group by deptno;
Result:
DEPTNO ENAMES ---------- ------------------------------ 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
Oracle: functions to join and split strings in sql
Join:
create or replace
function join
(
p_cursor sys_refcursor,
p_del varchar2 := ','
) return varchar2
is
l_value varchar2(32767);
l_result varchar2(32767);
begin
loop
fetch p_cursor into l_value;
exit when p_cursor%notfound;
if l_result is not null then
l_result := l_result || p_del;
end if;
l_result := l_result || l_value;
end loop;
return l_result;
end join;
/
select join(cursor(select ename from emp))from dual;
Split:
create or replace TYPE "SPLIT_TBL" as table of varchar2(32767);
/create or replace
FUNCTION split(
p_list VARCHAR2,
p_del VARCHAR2 := ',' )
RETURN split_tbl pipelined
IS
l_idx pls_integer;
l_list VARCHAR2(32767):= p_list;
BEGIN
LOOP
L_IDX :=INSTR(L_LIST,P_DEL);
IF l_idx > 0 THEN
pipe row(SUBSTR(l_list,1,l_idx-1));
l_list:= SUBSTR(l_list,l_idx +LENGTH(p_del));
ELSE
pipe row(l_list);
EXIT;
END IF;
END LOOP;
RETURN;
END split;
/
Now, you can run a query like this:
SQL> select * fromtable(split('one,two,three'));
one
two
three
one
two
three
Ref: create functions to join and split strings in sql
Subscribe to:
Posts (Atom)