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?

Answer 1

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