Search code examples
postgresqlurlsubstringextract

How to extract relative path from a URL in postgresql

I have an URL and I want to extract everything other than host name. How can this be achieved? For eg:

My string: https://title001-stg.azuge.net/enstgormedia/media/802/titles/123.srt

My requirement: media/802/titles/123.srt

Solution

  • you can make use of PG's REGEXP_REPLACE

    For example if you have table and values like the below

    create table testexample (url varchar);

insert into testexample values ('https://title001-stg.azuge.net/enstgormedia/media/802/titles/123.srt');
insert into testexample values ('https://title002-stg.azuge.net/enstgormedia/media/804/titles/123.srt');

    You could extract the part you want with

    select REGEXP_REPLACE(url, 'https://[a-zA-Z0-9\-]+.[a-zA-Z0-9\-]+.[a-z]+\/[a-z]+\/','') from testexample;

    The regular espression https://[a-zA-Z0-9\-]+.[a-zA-Z0-9\-]+.[a-z]+\/[a-z]+\/ mimics the http://a.b.net/folder/ setup in your example