Search code examples
sqlregexoracle-databaseregexp-replace

Using RTRIM or REGEXP_REPLACE to replace a comma with a comma space and single quote

I'm attempting to learn Oracle regexp_replace well enough to take a value stored in a table as a comma-separated string and change the comma character with a single quote followed by a comma followed by a space, followed by a single quote.

For instance, the field (CourseListT) contains course codes that look like this:

PEOE100,H003,H102,L001,L100,L110,M005,M020,M130

I want it to look like this:

'PEOE100', 'H003', 'H102', 'L001', 'L100', 'L110', 'M005', 'M020', 'M130'

I started with baby steps and found article #25997057 here that showed me how to insert spaces. So I have this working:

SELECT 
  regexp_replace(gr.CourseListT,'([a-zA-Z0-9_]+)(,?)','  \1\2')
FROM gradreq gr
WHERE gr.gradreqsetid = 326
AND gr.SubjectArea = 'Electives'

But nothing I do will allow me to insert those silly single quote marks.

Would it be better to learn RTRIM replace? Could somebody please help me learn how to accomplish this?

Thank you Schelly

Solution

  • You can simply do it with replace. Use double single-quotes to escape a single-quote.

    select '''' || replace(CourseListT, ',', ''', ''') || '''' 
from gradreq