How do use MySql Regex inside locate?

Question

If I try to use Regex inside locate it fails

Select Locate(FieldA regexp '[a-z][A-Z][a-z]',Binary FieldA) from  PatternTester

as per http://sqlfiddle.com/#!9/403c36/2.

If I search for the explicit letter pattern it locates it correctly:

Select Locate('lC',Binary FieldA) from  PatternTester

as per http://sqlfiddle.com/#!9/403c36/6

Is there something I need to do to make locate 'obey' Regex or will it simply not?

[REGEXP_INSTR()](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-instr) — Akina, Aug 06 '20 at 18:22
@Akina My MySql Version 5.6.47 says the command does not exist. — user3649739, Aug 06 '20 at 18:30
You are checking to see of a string has _any_ camelcase in it? Or do you necessarily need to "locate" it, also? — Rick James, Aug 09 '20 at 03:55
@Rick James. I actually want to extract it. I found a way to do so if I look for a specific pattern eg `lC` with a few embedded `substring_index` statements which included '`Binary` but now the trick would be to replace the explcit pattern with a regex instead. — user3649739, Aug 09 '20 at 12:03

score 1 · Accepted Answer · answered Aug 06 '20 at 18:39

As mysql document says, LOCATE() will returns the position of the first occurrence of substring substr in string st, so it will not take any regex as input argument.

Also from checking your fiddle reference, you don't have REGEX_INSTR in this version!

How do use MySql Regex inside locate?

1 Answers1