Mysql regex alphanumerical with some special chars

Question

Alright, I've had a some nightmares with it already so i bow my stupid head to the almighty hive-mind of the Stackoverflow.

UPD: the parameters of my task have been changed. I need to find the codes that could include special chars like parentheses (), and/or @, #, &, $.

So the codes could look like:

"A", "Bb", "2C8", "A7(BO)19", B29H$, 29H(6JI)0# etc

The problem is that all these codes are optional. I've tried like Barmar (see reply 1) suggested, but slightly modifing the MySQL query:

SELECT * FROM table WHERE column REGEXP '^[a-z0-9\@\#\$\&()]*$' AND column LIKE CONCAT('%', '$v', '%')

It cannot return me "S(LJ)9" or "09S(LJ)3$" if i seek for "SLJ" or "S(LJ)"

Well, aside some real important nucleotide sequence in my DNA that would allow me to use brains more efficiently (or have them), what am i missing in this regex or the query itself? Thanks anyone in advance.

Are the characters the user inputs required to be in the value? — Barmar
– Barmar, Commented Apr 11, 2013 at 22:30

Barmar · Accepted Answer · 2013-04-12 15:19:33Z

2

SELECT * FROM table WHERE column REGEXP '^[a-z0-9()]*$' /* code contains alphanumerics and parentheses */ AND column NOT REGEXP '[0-9]{3}' /* code does not contain >2 digits in a row */ AND column LIKE CONCAT('%', ?, '%') /* code contains the user input */

where ? is bound with the user input (you should be using PDO or mysqli prepared statements).

edited Apr 12, 2013 at 15:19

answered Apr 11, 2013 at 22:27

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rhiozan Over a year ago

Thanks, Barmar! The user can input up to 5 codes and they can be randomly met in 5 cols. The logic is to find rows where all the codes could be found in any of the specific columns:

SELECT * FROM table WHERE ID IN(SELECT ID FROM table WHERE (((col0 REGEXP '^[\w()]*$' AND col0 LIKE CONCAT('%', '$v', '%'))OR ... OR(col4 REGEXP '^[\w()]*$' AND col4 LIKE CONCAT('%', '$v', '%')))AND ... AND((col0 REGEXP '^[\w()]*$' AND col0 LIKE CONCAT('%', '$v5', '%'))OR ... OR(col4 REGEXP '^[\w()]*$' AND col4 LIKE CONCAT('%', '$v5', '%')))) and othercol is NOT null)

It sadly returns nothing

Barmar Over a year ago

Mysql doesn't have \w and \d regexp escape sequences, I've changed my answer to expand these. Try this form.

Rhiozan Over a year ago

Thanks again, Barmar. After the last update of your solution it works good enough. Sadly the project im working with is very sensitive to such search in the db. I will try to master the art of regex :)

Collectives™ on Stack Overflow

Mysql regex alphanumerical with some special chars

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related