Mysql Regex query find first latter and begins with given work/character

Question

I have pre-build application and in this application, there are some MySQL queries which are executing Regex queries. And I'm trying to use those queries other place. When I'm using same Regex query other place then those are not working.

Here is my query

SELECT * FROM `table_name`
WHERE field_name REGEXP '^[[:<:]]e[^[:space:]]* k.*'

When I'm executing this query in MySQL then getting this message:-

#3685 - Illegal argument to a regular expression.

I'm stuck here. Please help me to resolve issue.

Edited:- Wiktor Stribiżew as you told to use \\b if I'm using [[:<:]] or [[:>:]]. But query is not providing any result. Below is the query which I'm executing:-

SELECT * FROM `table_name` WHERE field_name REGEXP "\\b'what that'\\b"

This part: `[:<:]` makes the regular expression invalid. I don't know what you are trying to do, you can test other variations of that part. — forpas, Jul 13 '19 at 14:40
@forpas yes `[:<:]` was invalid so corrected this to `[[:<:]]` — Lakhwinder Singh, Jul 13 '19 at 14:48
You are using MySQL 8, right? Use `REGEXP '^e\\S* k'`. Wherever you have `[[:<:]]` or `[[:>:]]`, replace with `\\b` — Wiktor Stribiżew, Jul 13 '19 at 17:37
Why did you use `REGEXP "\\b'what that'\\b"`? I suggested `REGEXP '\\bwhat that\\b'`. But now, you say it is MySQL 5.6, your `[[:<:]]text[[:>:]]` should work. Please provide sample record you want to be returned with this regexp query. — Wiktor Stribiżew, Jul 14 '19 at 07:56
It does work since it finds `it is what that here` and does not find a match in `wwhat that`. But you seem to want `a.*b|b.*a` pattern, `what.*that|that.*what` — Wiktor Stribiżew, Jul 14 '19 at 08:02
Yes I found solution. Basically on my local system I have 8.0 and on production server I have 5.7. So I did some research and find solution for both. Thanks buddy. — Lakhwinder Singh, Jul 16 '19 at 14:43
Does it mean the https://stackoverflow.com/questions/57020006/mysql-regex-query-find-first-latter-and-begins-with-given-work-character?noredirect=1#comment100580887_57020006 comment works for you? `REGEXP '\\bwhat that\\b'` and `[[:<:]]text[[:>:]]`? — Wiktor Stribiżew, Jul 19 '19 at 21:36

Mysql Regex query find first latter and begins with given work/character

0 Answers0

Linked