removing commas in postgresql query

Question

I have a query:

SELECT phone, 
       to_char(appt_date,'MM/DD/YYYY'), 
       time_text, 
       staff_email || subject_line as title,
       staff_wav,
       agency_wav 
FROM phone_dialer

that is sent to a csv file

That results in

9105554444,07/01/2011,08:30am,me@myemail.orgGADK082493,staffid0827,Coastal Counseling

or

9105554444,07/01/2011,08:30am,me@myemail.orgGADK082493,staffid0827,Smith, Bob

The "agency_wav" column could have a name of company. I have tried several ways to remove the comma between Smith, Bob and am failing miserably.

Can anyone steer me to a resolution?

Answer 1

Answer to title, since the body of the question is unclear.

Fastest way to remove commas from a string:

SELECT translate('Smith, Bob, foo,,,bar', ',', '');

Related answer addressing translate() / replace():

• Looking for phone number containing a minus, like "123-456789"

Answer 2

The preferred way to create CSV is to use COPY command.

If by some reason you don't want or can't use it, you just need make value returned in the column CSV friendly that is enclose value in double quotes and escape existing double quotes by duplicating them in the string. This will preserve correct value (that is all commas) but will not break CSV format.

SELECT phone, 
       to_char(appt_date,'MM/DD/YYYY'), 
       time_text, 
       staff_email || subject_line as title,
       staff_wav,
       '"' || replace(agency_wav, '"', '""') || '"'
FROM phone_dialer

This will produce the following line

9105554444,07/01/2011,08:30am,me@myemail.orgGADK082493,staffid0827,"Smith, Bob"

Note quoted value which has comma.

Answer 3

If your surround your query with the syntax COPY () TO STDOUT WITH CSV; then it will construct the CSV output and automatically quote the field values that contain commas.

If you want to manually do it in the query, try replace(agency_wav,',','').