How to escape “@” on REGEX mysql?


Problem :

I have this registry ([email protected]) in tableMail. If I use

    REGEXP '[[:<:]]my[[:>:]]' 

[email protected] is found. If I use

    REGEXP '[[:<:]][email protected][[:>:]]' or REGEXP '[[:<:]]my\@[[:>:]]'

[email protected] is not found.

I think it is because "@" is a especial character. So how to escape it?



Solution :

The "@" is not a special character in regular expression syntax. The match is failing because "m" is not a word boundary character, and you're forcing it to match word boundaries with [[:>:]].

Demo:

mysql> select '[email protected]' regexp '[[:<:]]my[[:>:]]';
+-----------------------------------------+
| '[email protected]' regexp '[[:<:]]my[[:>:]]' |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

mysql> select '[email protected]' regexp '[[:<:]][email protected][[:>:]]';
+------------------------------------------+
| '[email protected]' regexp '[[:<:]][email protected][[:>:]]' |
+------------------------------------------+
|                                        0 |
+------------------------------------------+

mysql> select '[email protected]' regexp '[[:<:]][email protected][[:>:]]';
+----------------------------------------------+
| '[email protected]' regexp '[[:<:]][email protected][[:>:]]' |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+

mysql> select '[email protected]' regexp '[[:<:]][email protected][[:>:]]';
+-----------------------------------------------+
| '[email protected]' regexp '[[:<:]][email protected][[:>:]]' |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+

So take off the word boundary pattern if the next character in your string is not punctuation.


Mysql Tutorials

Mysql Howto..

How to define when you should hide “Show More” button?

PHP & MySQL - How should I allow my members to delete their membership from my site?

how to display query results in sets, increment on user click php?

Mysql: How to insert into table using select where clause

how can i change this mysql code to pdo?

How can I rewrite my PHP & MySQL to group my HTML list by equal column values?

how to send multiple row to mysql at same time(like bulk copy)?

MySQL table with indexes: do many inserts/updates influence performance? If yes, how to speed it up again?

how to establish connection to database using PHP MySql

How can I use now() in Doctrine 2 DQL?

MySQL - How to separate out multiple table rows into one like query result

how to see spaces in data when selecting with mysql command line client

How to insert large amount of data in a MySQL table without LOAD DATA INFILE?

How to get correct onclick ID from PHP loop for Ajax call?

how to retreive the last updated data from mulitple categories using php and mysql

MySQL- How to implement event scheduler everyday + start from 8am to 10 pm+ every 15 min

How to send a parameterized DELETE to MySql from Java

How to sync two database tables with each other in mysql PHP

SQL and DateTime - How do I select all rows that have existed for a specific time period?

How do I escape % from python mysql query

How to use own function to replace array element in array returned from MySQL

How to debug JPA CriteriaBuilder queries

How to do this query against MySQL database table?

how to prevent conversion of %(number) to code

MySQL - How to select random after select top?

MySQL: how to subtract an update

how to clean Laravel Bootstrap cache config file?

How to delete rows from a mysql database using php with a form delete button

How to import sql files which are more than 10mb in mysql database

MySQL: how to convert to EAV - Part 2?