0

I have a SQL table with the following values:

+---------+----------+
| post_id |   path   |
+---------+----------+
|       1 | 1/       |
|       2 | 1/2/     |
|       3 | 1/2/3/   |
|       4 | 1/2/3/4/ |
|       5 | 1/2/5/   |
+---------+----------+

How can I create a query that would get the path with the exact number of values separated by slashes?

For example, if I wanted all post_ids where the path is exactly 1/%/%/ (where each % represents a single number), meaning return anything of the form 1/2/3/, 1/2/5/, but not 1/2/3/4/.

Improve this question

2 Answers 2

Reset to default
1

Here's one option using regexp:

select *
from yourtable
where path regexp '1/[0-9]/[0-9]/$'
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Does regexp need to be enabled somewhere for this to work, because I can't get it to show any results.
Also, your answer doesn't seem to work properly. I tried removing the second [0-9]/, and it's returning all results when it should only be returning one: sqlfiddle.com/#!2/d0c28/7/0
@user4171336 -- sorry, left out the $ for end of string... sqlfiddle.com/#!2/d0c28/9 -- in regards to regexp not working, I honestly don't know why...
I changed the values of the paths to something higher (2000) and the query no longer works: sqlfiddle.com/#!2/fdce76/1/0
@user4171336 -- That's because [0-9] only matches on a single character (as we assumed from your post). I see you now have the correct solution. Glad we could help.
0

There are several ways to do that:

MySQL LIKE operator.

The LIKE operator provides two wildcard characters, the percentage % ( match any string of zero or more characters), and underscore _ ( match any single character ).

SELECT * FROM `table` WHERE `path` LIKE '1/_/_/'

SELECT * FROM `table` WHERE `path` LIKE '1/%/%/'

MySQL Regular Expressions.

SELECT * FROM `table` WHERE `path` regexp '^1/[0-9]/[0-9]/$'

Hierarchical Data in MySQL

Since this structure involves hierarchical data maybe you should consider to change the table structure to something that represents actual hierarchy. http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ has an excellent tutorial about the subject.

Improve this answer

2 Comments

Does [0-9] only check for values equal to 0-9 or does it also include much larger values, like 5,000?
Matches only one 0-9 characters, to match more than one use '^1/[0-9]+/[0-9]+/$'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.