Postgres SELECT statement where field value ends with

Question

I have a postgres database table called: web_data

Currently i'm running the following SQL select statement:

SELECT url_path FROM web_data WHERE url_path IS NOT NULL

Here are some example results I will get for url_path:

/
/aboutus.html
/services.php
/images/twitter_counter.png
/images/facebook_counter.png
/mobile/windows/TileTemplate.xml
/en_US/fbevents.js
/css/style.css

I would like to return results where the end value of url_path is either one of the following:

/
.html
.htm
.php
.asp
.aspx
.pdf

Is there a way to put this into the SQL statement instead of getting all results and then going through it with something like PHP?

score 32 · Accepted Answer · answered Mar 18 '16 at 14:38

32

Use the LIKE operator:

select url_path
from web_data
where url_path like '%.htm'
   or url_path like '%.html'
   or url_path like '%.php'
   or url_path like '%.asp'
   or url_path like '%.aspx'
   or url_path like '%.pdf'
   or url_path like '%/';

http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-LIKE

answered Mar 18 '16 at 14:38

so having the % only in the beginning mean it has to end like that? because with the LIKE syntax i usually put LIKE %.html% Also, if i have lots of records, is the LIKE function very slow? – Ahmed Mar 18 '16 at 14:52
@Ahmed: `%` is a wildcard `%.html` means any value that _ends_ with `.html`. And yes this is going to be slow because the database can't use a regluar index for that. You would need to create a [trigram](http://www.postgresql.org/docs/current/static/pgtrgm.html) index if you really have that many rows so that an index would help – Mar 18 '16 at 14:56

score 2 · Answer 2 · answered Oct 06 '22 at 09:07

You can use SIMILAR TO to match your end values.

SELECT 
  url_path 
FROM 
  web_data 
WHERE 
  url_path SIMILAR TO '%(.htm|.html|.php|.asp|.aspx|.pdf|/)';

This is actually rewritten to following regex match:

SELECT 
  url_path 
FROM 
  web_data 
WHERE 
  url_path ~ '(.htm|.html|.php|.asp|.aspx|.pdf|/)$';

Thus regex match is more performant then SIMILAR TO and you can use this one.

Postgres SELECT statement where field value ends with

2 Answers2