Search code examples
postgresqlurlregexp-replace

How to replace value in URL in postgresql

I am trying to replace 5e0361f5af70f400441148 in https://place.mycompany.com/analyst/5e0361f5af70f40044112148/dbt to id. In the end should receive https://place.mycompany.com/analyst/id/dbt

I use regexp_replace('https://place.mycompany.com/analyst/5e0361f5af70f400441148/dbt','[[:digit:]]','','g') but i do not know how to replace alphabet also

Solution

  • If that Id is always using lowercase a-f, the following will do:

    SELECT regexp_replace('https://place.mycompany.com/analyst/5e0361f5af70f400441148/dbt' , '/[0-9,a-f]+/','/id/');

    Otherwise add the A-F range: '/[0-9,a-f,A-F]+/'

    This will break if other parts of your url contain [0-9,a-f] characters only.

    If your input contains more than one id, add a 'g' modifier like so:

    SELECT regexp_replace('https://place.mycompany.com/analyst/5e0361f5af70f40044112148/dbt/5e036445af70f40b1012b48f/top-chart', '/[0-9,a-f]+/','/id/','g');