CODEWITHSUNDEEP
mysqlsql
Alex
Alex

Reputation: 12913

Select items containing a specific number of a given character (SQL)

For a table like this:

visits  url
594     http://example.com/page1/stuff
302     http://example.com/page2
494     http://example.com
202     http://example.com/page3/stuff/more

I would like to select only the urls that contain 4 / characters.

In python I would use len(url.split('/')) == 5. How can this be done in an SQL query?

Upvotes: 1

Views: 39

Answers (1)

Barmar
Barmar

Reputation: 780984

This kind of search can't be indexed, so there's no way to optimize it. One way is with a regular expression:

WHERE url REGEXP '^([^/]*/){4}[^/]*$'

Explanation:

  • ^ = beginning of string
  • [^/]*/ = 0 or more non-/ characters followed by /
  • (...){4} = exactly 4 repetitions of the pattern inside the parentheses
  • [^/]* = another sequence of non-/ characters
  • $ = end of string

Upvotes: 1

Related Questions