Getting rid of comma at the end of PL/SQL Function [Oracle PL/SQL]

user_14539755 0
Viewed 65

Am working on a stored PL/SQL function that find the names of dependents of an employee by the given employee's number. So far I am able to get the desired output but seem to have an issue with the commas during the output. Any help is appreciated in regards to how I can remove the last bracket during the output.

[Current Code]

SQL> create or replace function FINDDEPENDENTS(empid in Employee.E#%TYPE)
  2  RETURN VARCHAR IS
  3  EID Employee.E#%TYPE;
  4  Ename Employee.Name%TYPE;
  5  
  6  DEPNAME Dependent.DName%TYPE;
  7  finalRow VARCHAR(2000);
  8  CURSOR q IS
  9  --
 10  select DName from Dependent WHERE E#=empid;
 11  begin
 12  select E#, Name INTO EID, Ename FROM Employee WHERE E#= empid;
 13  finalRow:= EID || ' ' || Ename || ': ';
 14  open q;
 15  loop
 16  fetch q into depname;
 17  if q%notfound then exit;
 18  end if;
 19  finalRow:= finalRow || DEPNAME || ', ';
 20  end loop;
 21  close q;
 22  return(finalRow);
 23  end FINDDEPENDENTS;
 24  /

Function created.

SQL> show errors
No errors.
SQL> SELECT FINDDEPENDENTS(E#) FROM Employee;

FINDDEPENDENTS(E#)
----------------------------------------------------------------------------------------------------
00100 Albert: Bolt, Edee, Judy,
00101 Peter:
00103 Ami:
00105 Robert:
00107 Wendy:
00109 Michael:
00110 Alvin:
00120 Alice: Blues, Edee, Kadi,
00125 Angela:
00136 Aban:
00150 Bob:
00187 Eadger:
00200 Carl: Eva,
00250 Douglass:

14 rows selected.

How can I get rid of the last comma so that I can get an output like:

00100 Albert: Bolt, Edee, Judy
00110 Alvin:
00120 Alice: Blures, Edee, Kadi
plsql function oracle
3 Answers

TRIM it:

return(rtrim(finalRow, ','));
user_90979060

You can use RTRIM as mentioned in one of the answers, But you can also use the following logic which will prevent the creation of such data.

Use

finalRow:= finalRow || CASE WHEN SUBSTR(finalRow ,-2) <> ': ' THEN ',  ' END || DEPNAME;

Instead of

finalRow:= finalRow || DEPNAME || ', ';
user_115656290

Use TRIM function:

create or replace function foo (s varchar2) return varchar2 is
begin 
    return trim (trailing ',' from s);
end;
/
var ret varchar2 
exec :ret := foo ('foo,') 

RET
---
foo
user_65710200
Related