CODEWITHSUNDEEP
mysqlregex
deer deer
deer deer

Reputation: 49

MySQL Regexp error

I have a table files with rows as follows:

+-----+----------------------+----------+
| id  | file_path            | owner_id |
+-----+----------------------+----------+
| 332 | /path/to/file1/      |      150 |
| 333 | /path/to/file1file1/ |      150 |
| 334 | /path/to/file2/      |      151 |
| 335 | /path/to/file3/      |      152 |
| 336 | /path/to/file4/      |      150 |
| 337 | /path/to/file5/      |      150 |
| 338 | /path/to/file6/      |      151 |
+-----+----------------------+----------+

and using this MySQL query: 

select * from files WHERE file_path REGEXP '(file1){1}';

Produces this: 

+-----+----------------------+----------+
| id  | file_path            | owner_id |
+-----+----------------------+----------+
| 332 | /path/to/file1/      |      150 |
| 333 | /path/to/file1file1/ |      150 |
+-----+----------------------+----------+

But with my understanding, it should produce this:

+-----+-----------------+----------+
| id  | file_path       | owner_id |
+-----+-----------------+----------+
| 332 | /path/to/file1/ |      150 |
+-----+-----------------+----------+

What's wrong? How should I modify the query to produce wanted results? I want to get rows which contain string (file1) ONCE and ONCE only.

Thank you very much in advance.

EDIT:

+-----+-----------------------+----------+
| id  | file_path             | owner_id |
+-----+-----------------------+----------+
| 349 | /path/to/file1/       |      158 |
| 350 | /path/to/file1file1/  |      158 |
| 351 | /path/to/file1X/      |      158 |
| 352 | /path/to/file1Xfile1/ |      158 |
| 353 | /path/to/file2/       |      159 |
| 354 | /path/to/file3/       |      160 |
| 355 | /path/to/file4/       |      158 |
| 356 | /path/to/file5/       |      158 |
| 357 | /path/to/file6/       |      159 |
+-----+-----------------------+----------+

With this table I want to input file1 and get /path/to/file1/, file1X and get /path/to/file1X/ etc etc.

Upvotes: 1

Views: 240

Answers (1)

vks
vks

Reputation: 67968

select * from files WHERE file_path REGEXP '(\/file1\/)';

This should work.Your regex wont work cause it says "pick line having 1 line1 which both lines satisfy.

EDIT:

use this 

\/file1[^\/]*\/

to match line1x

Upvotes: 1

Related Questions