CODEWITHSUNDEEP
regexpostgresqlreplace
Thomas Price
Thomas Price

Reputation: 101

regex_replace using a pattern in the replacement text

Is it possible to use a pattern in regex_replace in the replacement text? I'm trying to insert a space between names.

For example with Johnny SmithSarah Suzanne(D)Johnny SmithSarah Suzanne(D), I am trying to get the output to be Johnny Smith Sarah Suzanne(D) Johnny Smith Sarah Suzanne(D)

I've tried something like this:

SELECT 
    'Johnny SmithSarah Suzanne(D)Johnny SmithSarah Suzanne(D)'
    , regexp_replace('Johnny SmithSarah Suzanne(D)Johnny SmithSarah Suzanne(D)', '[a-z)][A-Z]',' ', 'g');

I realize my pattern is wrong to begin with... (still trying to figure it out) but before I continue, is it even possible? Can someone give me guidance on how it's done?

Upvotes: 0

Views: 197

Answers (2)

Hambone
Hambone

Reputation: 16397

I might be oversimplifying this, but can you not just replace the ')' character with a ') '?

select regexp_replace ('Johnny Smith(D)Johnny Smith(D)', '\)', ') ', 'g')

If you're worried about adding an extra space at the end, you can always change it to only do it when the ')' is followed by a character set (example below word character):

select regexp_replace ('Johnny Smith(D)Johnny Smith(D)', '\)(\w)', ') \1', 'g')

Upvotes: 0

sticky bit
sticky bit

Reputation: 37487

You seem to look for capture groups.

SELECT 'Johnny SmithSarah Suzanne(D)Johnny SmithSarah Suzanne(D)',
        regexp_replace('Johnny SmithSarah Suzanne(D)Johnny SmithSarah Suzanne(D)', '([a-z)])([A-Z])','\1 \2', 'g')

If you enclose parts in the pattern with parenthesis (without any special characters directly after the (, that change the meaning of the group), the string matching that part of the pattern gets captured. With '\' you can reference these captured strings in the replacement expression and the matched string will be placed there. n is the number of the capture group from left to right, child before parent when nested.

Upvotes: 1

Related Questions