CODEWITHSUNDEEP
mysqlsqlregex
Eduardo Ponce de Leon
Eduardo Ponce de Leon

Reputation: 9696

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.

Upvotes: 2

Views: 594

Answers (2)

Rick James
Rick James

Reputation: 142306

WHERE LENGTH(name) = 2 +
      LENGTH(REPLACE(name, '/', ''))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270001

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.

Upvotes: 8

Related Questions