Pattern matching in SQL with a website url

Question

I am working with a table in SQL that has a 'url' column and I am trying to write a query that pattern-matches some of this url column. For example, I have this table in my schema named 'pages':

     name                       url 
1   timmy  www.timmy.com/aboutme/p1
2    fred     www.getme.com/product
3     tom  www.skyme.com/aboutme/r3
4     joe     www.five5.com/aboutme
5   chris     www.chris.com/contact
6    rich     www.noway.com/contact

And I'd like to query the table by pattern matching .com/aboutme in the url. That is, I'd like to return rows 1,3, and 4 because the substring ".com/aboutme" exists in the strings in the url column for these rows. Is this possible? As a follow up, how could this be done if I'd like to pattern-match multiple patterns "match on .com/aboutme AND .com/contact"

Any help is appreciated, as always!

select * 
  from pages

where url matches substring...

EDIT: This seems like an obvious question that's been asked before - if so, feel free to point me in the right direction on this.

jeprubio · Accepted Answer · 2018-01-19 23:52:25Z

3

You can use SQL wildcard characters for this:

SELECT * FROM pages
WHERE url LIKE '%.com/aboutme%'

An example with multiple matches:

SELECT * FROM pages
WHERE url LIKE '%.com/aboutme%'
OR url LIKE '%.com/contact%'

edited Jan 19, 2018 at 23:52

answered Jan 19, 2018 at 23:47

jeprubio

18.1k5 gold badges51 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pattern matching in SQL with a website url

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related