CODEWITHSUNDEEP
mysqlregex
swapfile
swapfile

Reputation: 417

mysql select regexp like with "full-stop"

have that little sql select: 

select * from import_daten WHERE lastname REGEXP 'dipl\.|dr\.';

And just want to filter the rows with ing. and dipl. but with that statement i also get the people wtih for e.g. "Abendroth" in Lastname. Because the "dr" in Name.

Same is with

select * from import_daten WHERE lastname REGEXP 'dipl.|dr.';

How is it possible to include the full-stop correct within the regexp?

Upvotes: 2

Views: 744

Answers (3)

Rick James
Rick James

Reputation: 142366

REGEXP '(dipl|dr)[.]'

Be careful of start/end of word:

mysql> SELECT 'dr.' REGEXP 'dr[.][[:>:]]', 'dr.' REGEXP 'dr[.]';
+-----------------------------+----------------------+
| 'dr.' REGEXP 'dr[.][[:>:]]' | 'dr.' REGEXP 'dr[.]' |
+-----------------------------+----------------------+
|                           0 |                    1 |
+-----------------------------+----------------------+

Notice how it fails? That is because . is not a character that can exist in a 'word'.

Also, I used [.] instead of \. because of the problem of escaping the escape character -- in some situations you need \\.; in others you might need \\\\.. Too confusing.

If necessary you can use 'word start': REGEXP '[[:<:]](dipl|dr)[.]'

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562651

You probably want to make sure the pattern is at a "word boundary." MySQL's regular expression syntax has special character sequences for that:

select * from import_daten WHERE lastname REGEXP '[[:<:]](dipl\.|dr\.)[[:>:]]';

See http://dev.mysql.com/doc/refman/5.7/en/regexp.html. It's nearly the last item on the page before that page's user comments.

Upvotes: 1

Chris Lear
Chris Lear

Reputation: 6742

I think you want this

select * from import_daten WHERE lastname REGEXP '(dipl\.)|(ing\.)';

Upvotes: 1

Related Questions