PHP MySQL WHERE Clause with condition

Question

MySQL Table video

id | path
4  | 89_1232
6  | 90_2121

I need to match path before underscore _. For instance 89 , 90. Is it possible with MySQL query.

Right now what I am doing is, fetching all rows and then explode path row:

"SELECT `path` FROM `video`

then foreach () { explode and check }

I'm worried you have put two separate pieces of data in one column, consider separating these out into two columns, then you can index and match all the parts independently.. you can always concat on select if you need the original display. — Arth
– Arth, Commented Nov 10, 2015 at 12:44

Ben · Accepted Answer · 2015-11-10 12:40:19Z

3

Use LIKE with wildcards

Example:

SELECT `path` FROM `video` WHERE `path` LIKE '89\_%' ESCAPE '\'

which will search for rows with the pattern 89_{anything}, using the backslash \ as the escape character (to make sure the underscore _ is literal)

edited Nov 10, 2015 at 12:40

answered Nov 10, 2015 at 12:37

Ben

8,9519 gold badges48 silver badges82 bronze badges

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

2 Comments

Arth Over a year ago

Actually underscore means one of any character unless escaped.

Arth Over a year ago

I'd also point out, that as this is the left most portion of the string, it can still use an index on the column.. which is why I'd use this method over SUBSTRING_INDEX.

Collectives™ on Stack Overflow

PHP MySQL WHERE Clause with condition

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related