multimediaxp multimediaxp - 3 months ago 4
MySQL Question

How to match strings that have exactly 2 slashes in MySQL

I have a database with a lot of paths, and I need to find all the paths that have only 2 levels, not more not less.
For example, I need a query that will find string matching the following structure:

folder/folder/file.ext


But not:

folder/file.ext
and not
folder/folder/folder/file.ext
or anything longer

My guess here is to use REGEX and match strings that precisely have 2 slashes
/
but I don't know how to formulate the expression, something like:

SELECT `name` FROM `table` WHERE `name` REGEXP '????'


In my case I need to find 2 slashes and is very specific but ideally this answer will be useful for anybody looking for 3 or X number of slashes or any other character repeated on the string.

Answer

The simplest method uses like:

where name like '%/%/%' and
      name not like '%/%/%/%'

Doing this as a regular expression is tricky. But here is another method:

where length(name) - length(replace(name, '/', '')) = 2

As a regular expression:

where name regexp '^([^/]*[/]){2}[^/]*$'

So it is possible, although perhaps less scrutable.

Comments