Search code examples
mysqlregexlocate

How do use MySql Regex inside locate?

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?

Solution

  • 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!