CODEWITHSUNDEEP
regexsearchmariadb
Barkermn01
Barkermn01

Reputation: 6842

MariaDB regex working in online regex testers does not work in SELECT WHERE REGEXP

I'm having a problem a dataset I have been given in bad format E.G fullName column and no breakdown of names I'm wanting to search where any of the names start with a given letter E.G 'J'

So this is my Statement but I just get complaints about unexpected REGEXP

SELECT * FROM `Officers` WHERE `fullName` REGEXP '.*\sJ.*';

Is there any way to do this in MariaDB, unfortunately, the names are not of a fixed word count some are only 2 names others are 6 names long so 4 middle names.

Upvotes: 6

Views: 4604

Answers (2)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626960

You may use

REGEXP '\\bJ'
        ^^^

Here, the \b is a word boundary that will force a match only when J is not preceded with a letter, digit or _.

The \ is doubled because the regex engine expects a literal \, and two backslashes are required.

Upvotes: 4

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6309

Try using something like this:

SELECT * FROM `Officers` WHERE `fullName` REGEXP '[[:<:]]J'

See docs: https://dev.mysql.com/doc/refman/5.7/en/regexp.html

Upvotes: 1

Related Questions