Postgres regex: add comma

Question

I expect this is an easy answer, but I can't figure out the right terms to search for.

I would like to add a comma in between any company name and "INC" (or, as it may be, "LLC", "LP", etc.). For example, I want 'COMPANY INC' to be 'COMPANY, INC' where 'COMPANY' could be any company name you might think of. It could have letters, numbers, but probably not special characters.

REGEXP_REPLACE("COMPANY A LP', '[a-zA-Z0-9] LP', ', LP') of course only returns 'COMPANY , LP'; how do I keep the A so that it returns COMPANY A, LP?

EDIT: There are also already valid records with the appropriate comma, i.e. COMPANY A, INC also already exists.

Answer 1

This seems to work:

test=> select REGEXP_REPLACE('COMPANY A LP', ' LP$', ', LP');
 regexp_replace
----------------
 COMPANY A, LP
(1 row)

This is even better:

test=> select REGEXP_REPLACE('COMPANY A LP', ' (INC|LP|LLC)$', ', \1');
 regexp_replace
----------------
 COMPANY A, LP
(1 row)

test=> select REGEXP_REPLACE('COMPANY A INC', ' (INC|LP|LLC)$', ', \1');
 regexp_replace
----------------
 COMPANY A, INC
(1 row)

(works for any of the suffixes you mentioned, in a single statement).

Or, if as per the comments, some of the records already have the comma:

test=> select REGEXP_REPLACE('COMPANY A, INC', '([^,]) (INC|LP|LLC)$', '\1, \2');
 regexp_replace
----------------
 COMPANY A, INC
(1 row)

test=> select REGEXP_REPLACE('COMPANY A INC', '([^,]) (INC|LP|LLC)$', '\1, \2');
 regexp_replace
----------------
 COMPANY A, INC
(1 row)

Another way to deal with that, if you are updating the table so that all the records are formatted uniformly, is to leave the ones that are not formatted incorrectly alone:

UPDATE xxx 
SET company_name=REGEXP_REPLACE(company_name, ' (INC|LP|LLC)$', ', \1') 
WHERE company_name ~ '[^,] (INC|LP|LLC)$';