Skip to main content

Oracle Split , Join , and DateDiff Functions

Split, Join and DateDiff are just few Oracle functions that can aid you in data manipulation.


Function Definition


Split - split a result by “character specified (by default, comma)”
into multiple results.


e.g. “A,B,C” –> results 3 records of “A”,”B”, and “C”.


Join - join multiple records into one result by
“character specified (by default, comma)”.


e.g. “1″,”2″,”3″ –> results 1 record of “1,2,3″.


DateDiff - Differentiate two dates.


Function Code



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;
l_value varchar2(32767);
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;


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;


create or replace function datediff( p_what in varchar2,
p_d1 in date,
p_d2 in date ) return number
as
l_result number;
begin
select (p_d2-p_d1) * decode( upper(p_what), 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL ) into l_result from dual;

return l_result;
end;


* Note : To use the split and join function, you must execute this code.


create or replace type split_tbl as table of varchar2(32767)

Comments

Popular posts from this blog

Creating Bottom-up Web Service (WSDL)

This post will primarily show you how to create a simple Web Service application through Apache Axis in Eclipse , and will not dwell on explaining the background or functionality of a Web Service. Yet, it’s a de facto to at least give a little definition. WSDL or the Web Services Definition Language is just another specification to describe network XML-based services. It supports message-oriented and procedural approach XML technologies. (for further reading click here ) 1. Preparing the web application a. Create a new web application and name it as “SimpleWebService”. b. Download and add “axis.jar” ( download here ) to the application libraries. c. Edit and add this following configurations to the web.xml file. AxisServlet org.apache.axis.transport.http.AxisServlet AdminServlet org.apache.axis.transport.http.AdminServlet 100 AxisServlet /servlet/AxisServlet AxisServlet *.jws AxisServlet /services/* *Note: spa...

How to get rid of VB Script Just-In-Time Debugger Error

Lately i have been pestered with a lame error every time my Windows starts up. The “VB Script Just-In-Time Debugger Error” shows up and it would terminate the explorer.exe process upon clicking OK. Somehow something went wrong in the system but the error does not specifically says what it is. I don’t have a clue how to resolve it. We all know explorer.exe is critical for all windows to work, thus leaving me no choice but to run it manually. For normal users who do not know how to run the explorer.exe manually, they will be paralyzed. They won’t find their way to work it except to ask for help, which sometimes can be so annoying specially when you’re up to finish a deadline. Luckily, i was so persistent enough to search for a solution. Though no one gave the exact process of eliminating this error, I come up to finally solve it through my compilation of readings and i’ll share it with you. Here's how to get rid of this error: 1. Open Regedi...

Search Engine Optimization (SEO)

Often we focus our site development mainly on its GUI (Graphical User Interface) and content, although this is very critical and needed, yet we tend to neglect adding some essentials that also need to be considered and included in order to optimize the site search ability. We want surfers to find and read our sites especially if we want to market something, thus it is important to make sure that our site link will be included in the list of search results on any search engines. The question is how will you do it? How will Google, Yahoo, msn etc, will find your site be indexed and included in their search results? There are a lot of things to learn and take into consideration if you want to optimize your site. Before proceeding, I’ll provide primary reference for you to get started before jumping into SEO. Read through this and decide if you need to reconstruct your site or not. Google Webmaster Site – This will acquaint you to search engines basic concepts. Webmaster Guidel...