Use postgres regexp_replace() to replace integers in a string

Question

I have a redshift database that I need to query, and need to group similar strings together. I'm using regexp_replace() to do this, but can't work out how to get it grouping strings that have integers in the middle. For example:

dataset:

string
'aaa/123/bbb'
'aaa/456/bbb'
'ccc/123/ddd'

I need to group this so that we'd get

string     count(*)
aaa/id/bbb 2
ccc/id/ddd 1

So I have tried using

regexp_replace(endpoint, '/[0-9]+$/', '/id/')

But it doesn't work, I'm assuming because there is no wildcard or something? But I can't work out how to fix this.

Thanks in advance

Gordon Linoff · Accepted Answer · 2019-09-16 16:42:10Z

1

I know that you also want to replace the numbers at the end. This comes close to what you want:

select regexp_replace(endpoint, '/[0-9]+(/|$)', '/id/')
from (select 'aaa/123/bbb' as endpoint union all
      select 'aaa/123' as endpoint 
      ) x

But it returns a slash at the end in the second case.

If you have no other intermediate values that start with numbers, then this does what you want:

select regexp_replace(endpoint, '/[0-9]+', '/id')
from (select 'aaa/123/bbb' as endpoint union all
      select 'aaa/123' as endpoint 
      ) x

Otherwise, two calls to regexp_replace() does the trick:

select regexp_replace(regexp_replace(endpoint, '/[0-9]+/', '/id/'), '/[0-9]$', '/id')
from (select 'aaa/123/bbb' as endpoint union all
      select 'aaa/123' as endpoint 
      ) x;

answered Sep 16, 2019 at 16:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use postgres regexp_replace() to replace integers in a string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related