Oracle PL/SQL - Remove Last Instance of a specific Character

Question

enter code hereI'm working on building a group of functions that return a json string based on the parameters and an internal select.

The problem that I'm running into is as i'm looping through the cursor in pl/sql, building the structure of the json, it adds an extra comma at the end of the last node.

It parses properly but the html eval of the json fails because of that extra comma. Is it possible to remove a specific character (say the very last comma in a string) in pl/sql. I've looked at REPLACE, but i cannot find documentation on using REPLACE with specific instances of a character.

The loop looks like:

FOR appGroup IN appGroups
LOOP

tsResults := tsResults || ' "Group" : { ';
tsResults := tsResults || ' "Id" : "' || appGroup.group_id || '", ';
tsResults := tsResults || '"Name" : "' || appGroupd.display_name || '" ';
tsResults := tsResults || ' }, ';

END LOOP;

It's that very last comma in the last row that is giving me the grief.

Is there any way to kill the last one w/o grabbing all of the string characters before it, and all of the characters after it and putting them together?

Any suggestions as to how I can get around this problem all together would be greatly appreciated as well.

Update

Using Cybernate's answer I was able to make some changes to get it to work... Originally immediately following the END LOOP; I had additional code adding more to the result string:

tsResults := tsResults ||  '}] }';

Which properly terminated the groups array that I was working on...

I placed the following code before that line:

tiLastComma := INSTR(tsResults, ',', -1);
tsResults := SUBSTR(tsResults, 1, tiLastComma - 1);

The json now is terminated properly and evals properly when used with jquery.

Solution 1

You can use INSTR function to find the position of the last occurance of the "," and then use SUBSTRING.

 SELECT SUBSTR(tsResults , 1, INSTR(tsResults , ',', -1)-1)
    INTO tsResults 
    FROM dual;

If you don't think Regular Expression is an overkill for this then use this statement in your PL/SQL:

 SELECT REGEXP_REPLACE(tsResults , '\s*,\s*$', '') 
    INTO tsResults 
    FROM dual;

Solution 2

Its easier to use the rtrim function. So after the loop:

tsResults := rtrim( tsResults, ',' );

Then to terminate the JSON, add the closing curly bracket in the same statement:

tsResults := rtrim( tsResults, ',' ) || '}';

Comments

[kjhughes]

Aashish, this would be better expressed as a comment on Tobi's answer. Until you have enough reputation to comment, please try to contribute answers that directly answer the question on their own.