Postgresql – more concise way in Postgres to extract a portion of a string

postgresqlselectstringsubstring

I'm using Postgres 9.5. I have a table with a column that records URLs. Sometimes the URL has a query string and sometimes it does not. I would like to extract the URL, minus any query string, so I came up with:

select substring(url, 0, case position('?' in url) when 0 then length(url)+1 else position('?' in url) end) 
from article;

This seems a little wordy and I was wondering if there's a more concise way to do this. My table column is of type TEXT.

Best Answer

You can use regexp_replace() to replace everything after the first ? with nothing:

select regexp_replace(url, '\?.*$', '')

The following example:

with data (url) as (
   values 
    ('http://foo.bar/some/page'),
    ('http://foo.bar/some/page?id=42&name=Marvin')
)
select url, regexp_replace(url, '\?.*$', '') as clean_url
from data;

returns:

url                                        | clean_url               
-------------------------------------------+-------------------------
http://foo.bar/some/page                   | http://foo.bar/some/page
http://foo.bar/some/page?id=42&name=Marvin | http://foo.bar/some/page
Related Question