Oracle PL / SQL - delete the last instance of a specific character

Question

enter code here I am working on creating a group of functions that return a json string based on parameters and internal selection.

The problem I am facing is that I iterate over the cursor in pl / sql, creating a json structure, add an extra comma at the end of the last node.

It parses correctly, but html eval json fails due to this extra comma. Is it possible to delete a specific character (for example, the most recent comma in a string) in pl / sql. I looked at REPLACE, but I cannot find documentation on using REPLACE with specific instances of a character.

The loop looks like this:

 FOR appGroup IN appGroups LOOP tsResults := tsResults || ' "Group" : { '; tsResults := tsResults || ' "Id" : "' || appGroup.group_id || '", '; tsResults := tsResults || '"Name" : "' || appGroupd.display_name || '" '; tsResults := tsResults || ' }, '; END LOOP; 

This is the last comma in the last line that gives me sadness.

Is there a way to kill the last one without capturing all the characters in the string before it and all the characters after it and adding them together?

Any suggestions on how I can get around this issue together would also be greatly appreciated.

Update

Using Cybernate's answer, I was able to make some changes to make it work ... Initially, right after END LOOP; I had extra code adding more to the result line:

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

Which correctly completed the array of groups I was working on ...

I placed the following code before this line:

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

Now json completes correctly and computes correctly when used with jquery.

Answer 1

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

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

If you don't think Regular Expression is redundant, use this statement in PL / SQL:

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

Answer 2

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

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

Then, to complete JSON, add a closing curly brace to the same statement:

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

Answer 3

Toby, I had a similar script, but remember that with RTRIM it will delete all instances of the value that you set in the function.

For instance:

I would like to trim the “X” at the end of the column selection. (LAST INSTANCE ONLY)

Column Value = 'SOMEVALUEXX'

Now RTrim will return: "SOMEVALUE"

Perhaps you are after = 'SOMEVALUEX'

Chandu's answer is correct.

Always check your business process.