How can I transform an "Or" SQL condition in LIKE Syntax (REGEX)?

Question

I am trying to write the following statement without the SQL OR:

SELECT * FROM table WHERE names LIKE 'john%' OR names LIKE 'brian%'

So, I don't want to use OR because the names in my real problem are too many, but I also don't want to use REGEXP. How is the LIKE syntax? Thank you in advance!

I don't want to use REGEXP because some names are in square brackets and others or not. And this make it too complicated. I just want to know if there is a syntax similar to 'REGEXP '^(".implode("|", $names_array).")[[:>:]]'";' using LIKE

If you want records of people whose name start with John or Brian, I think your query is just fine. Make sure you index names field — zedfoxus
– zedfoxus, Commented Sep 17, 2015 at 14:56
no, I said in the question, I have many names saved in an array. If I used REGEXP I would do this with implode and | as an OR. Something like this: 'REGEXP '^(".implode("|", $names_array).")[[:>:]]'";' But I need a similar syntax for LIKE — sotirios
– sotirios, Commented Sep 17, 2015 at 14:58
What about: SELECT * FROM table WHERE LEFT(names, 4) = 'john' OR LEFT(names, 5) = 'brian' as this should cover your needs? — Leptonator
– Leptonator, Commented Sep 17, 2015 at 14:59
Why do you want to use LIKE and not REGEXP? You cannot do what you want with LIKE, LIKE only have _ (one character) and % (any characters) symbol, you won't be able to create anything similar to a OR using LIKE. — Holt
– Holt, Commented Sep 17, 2015 at 15:02

Praveen Kumar Purushothaman · Accepted Answer · 2015-09-17 17:19:58Z

1

Use REGEX:

SELECT * FROM `table`
  WHERE `names`
  REGEX '^(john|brian)+'

edited Sep 17, 2015 at 17:19

answered Sep 17, 2015 at 14:56

Praveen Kumar Purushothaman

167k27 gold badges215 silver badges261 bronze badges

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

3 Comments

Mr. E Over a year ago

Correct me if I'm wrong but won't this only match names like "john" "johnjohn" "brianjohnbrian" etc ?

vp_arth Over a year ago

@Emi1305, no ^$ anchors here. So, 111john222 matches too.

vp_arth Over a year ago

Correct regex is ^(john|brian)

Collectives™ on Stack Overflow

How can I transform an "Or" SQL condition in LIKE Syntax (REGEX)?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related