Обсуждение: Needs Function
<img class="shape" height="0" src="cid:image001.jpg@01C78CCF.0463D980" style="display:none;width:0;height:0" v:shapes="_x0000_Mail"v:src="cid:image001.jpg@01C78CCF.0463D980" width="0" /><div class="Section1"><p class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="black" face="Times New Roman" size="3"><spanstyle="font-size:12.0pt;color:black">I need a user defined function for the following purpose….</span></font><pclass="MsoNormal"><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black">IfI pass a string with comma ( </span></font><b><font color="black" size="5"><span style="font-size:16.0pt;color:black;font-weight:bold">,</span></font></b><font color="black"><span style="color:black">)separated chars/values…. It should be appear in next line…</span></font><p class="MsoNormal"><font color="black"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black">Ex: select get_sep_str (‘SK, rp,h, j, 6, 9, kl’) from dual;</span></font><p class="MsoNormal"><font color="black" face="Times New Roman" size="3"><spanstyle="font-size:12.0pt;color:black">Output :</span></font><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">SK</span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">rp</span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">h </span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">j </span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">6 </span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">9 </span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">kl</span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold"> </span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">(Or)</span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><font color="black" face="TimesNew Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold"> </span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><font color="black"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold">I have one table like this…..</span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold"> </span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><u><font color="red"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:red; font-weight:bold">Temp Table:</span></font></u></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><u><fontcolor="red" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:red; font-weight:bold"><span style="text-decoration:none"> </span></span></font></u></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold">Deptno number(10)</span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold">Empno varchar2(200);</span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold"> </span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><u><font color="black"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold">Data in temp table:</span></font></u></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold"> </span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><font color="black"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold">Deptno Empno</span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold">----------- ------------</span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:124.5pt;margin-bottom:.0001pt;text-indent:-106.5pt;mso-list: l0 level1 lfo2"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold"><span style="mso-list:Ignore">10<font face="Times New Roman" size="1"><span style="font:7.0pt "Times New Roman""> </span></font></span></span></font></b><b><font color="black"><span style="color:black;font-weight:bold">B3091,B3092,B3093,B3085</span></font></b><pclass="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:124.5pt;margin-bottom:.0001pt;text-indent:-106.5pt;mso-list: l0 level1 lfo2"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold"><span style="mso-list:Ignore">11<font face="Times New Roman" size="1"><span style="font:7.0pt "Times New Roman""> </span></font></span></span></font></b><b><font color="black"><span style="color:black;font-weight:bold">3651,6521</span></font></b><pclass="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:124.5pt;margin-bottom:.0001pt;text-indent:-106.5pt;mso-list: l0 level1 lfo2"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold"><span style="mso-list:Ignore">12<font face="Times New Roman" size="1"><span style="font:7.0pt "Times New Roman""> </span></font></span></span></font></b><b><font color="black"><span style="color:black;font-weight:bold">H3062</span></font></b><pclass="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold"> </span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><font color="black"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold">Now, I want to display the data like this…..</span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold"> </span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><font color="black"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold">Deptno Empno</span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; font-weight:bold">--------- ---------</span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">10 B3091 </span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">10 B3092</span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">10 B3093 </span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">10 B3094</span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">11 3651</span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">11 6521</span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom: 0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black;font-weight: bold">12 H3062</span></font></b><p class="MsoNormal"><font color="black" face="Times New Roman" size="3"><spanstyle="font-size:12.0pt;color:black">Now, how can I achieve this….</span></font><p><font color="#006666" face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial;color:#006666"> </span></font></div><table><tr><td bgcolor="#ffffff"><font color="#000000">Informationtransmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customersand is intended for use only by the individual or the entity to which it is addressed, and may contain informationthat is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipientor it appears that this mail has been forwarded to you without proper authority, you are notified that any use ordissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately atinfo.in@infics.com and delete this email from your records.<br /></font></td></tr></table>
On 5/2/07, Penchalaiah P. <penchalaiahp@infics.com> wrote:
> I need a user defined function for the following purpose….
>
> If I pass a string with comma ( , ) separated chars/values…. It should be appear in next line…
>
> Ex: select get_sep_str ('SK, rp, h, j, 6, 9, kl') from dual;
>
> Output :
>
> SK
>
> rp
>
> h
>
> j
>
> 6
>
> 9
>
> kl
>
>
>
> (Or)
>
>
>
> I have one table like this…..
>
>
>
> Temp Table:
>
>
>
> Deptno number(10)
>
> Empno varchar2(200);
>
>
>
> Data in temp table:
>
>
>
> Deptno Empno
>
> ----------- ------------
>
> 10 B3091,B3092,B3093,B3085
>
> 11 3651,6521
>
> 12 H3062
>
>
>
> Now, I want to display the data like this…..
>
>
>
> Deptno Empno
>
> --------- ---------
>
> 10 B3091
>
> 10 B3092
>
> 10 B3093
>
> 10 B3094
>
> 11 3651
>
> 11 6521
>
> 12 H3062
>
> Now, how can I achieve this….
See:
http://archives.postgresql.org/pgsql-general/2005-12/msg00080.php
On 5/3/07, Rodrigo De León <rdeleonp@gmail.com> wrote:
Man, where was this last week when I needed it. I will have to keep this for future reference.
Thanks!
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
Man, where was this last week when I needed it. I will have to keep this for future reference.
Thanks!
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
Well, actually it was there last week too :)
>>> "Aaron Bono" <postgresql@aranya.com> 2007-05-03 16:14 >>>
On 5/3/07, Rodrigo De León <rdeleonp@gmail.com> wrote:
Man, where was this last week when I needed it. I will have to keep this for future reference.
Thanks!
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
>>> "Aaron Bono" <postgresql@aranya.com> 2007-05-03 16:14 >>>
On 5/3/07, Rodrigo De León <rdeleonp@gmail.com> wrote:
Man, where was this last week when I needed it. I will have to keep this for future reference.
Thanks!
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================