MySQL Regexp error

Question

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.

vks · Accepted Answer · 2014-11-12 12:02:52Z

1

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

edited Nov 12, 2014 at 12:02

answered Nov 12, 2014 at 10:58

vks

68.1k11 gold badges96 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

deer deer Over a year ago

select * from files WHERE file_path REGEXP '(file1){1,1}'; This produces the same result, but won't work correctly either - still produces 2 lines, shouldn't 1?

vks Over a year ago

@deerdeer this wont work cause you do not have anchors start and end.So if anywhere in line it finds line1 it will be a pass.

deer deer Over a year ago

Yours works fine for now, but I want mine to be different when I enter more. Editing main post with more information. Thanks a lot for helping, though.

vks Over a year ago

@deerdeer as per your latest example \/file1[^\/]*\/ should work

vks Over a year ago

@deerdeer [^\/]*\/ here [] is a character class , ^ means do not include whats der in the class.so the regex will travel till it finds \/.

|

Collectives™ on Stack Overflow

MySQL Regexp error

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related