CODEWITHSUNDEEP
mysqlregex
Gulshan Prajapati
Gulshan Prajapati

Reputation: 393

MySQL - REGEX Search word from string and matched word should be either alnum or numeric only

Question: If i pass any word in where clause then query should return only if passing word is alnum or num only using mysql regex.

I have a table where addresses.

address table 
--------------------
 id    | name
--------------------
  1    | 123 demo
--------------------
  2    | 1st demo
--------------------
  3    | 123
--------------------
  4    | demo

Example 1

SELECT * FROM address WHERE name regexp '(^|[[:space:]])123([[:space:]]|$)'

Result: Row 1,3 should return. it works for me

Example 2

SELECT * FROM address WHERE name regexp '(^|[[:space:]])1st([[:space:]]|$)'

Result: Row 2 should return. it works for me

Example 3

SELECT * FROM address WHERE name regexp '(^|[[:space:]])demo([[:space:]]|$)'

Result: It should not return any row. but it return 1,2,4 row

Final : So if i pass "demo" in where clause then no result should return.

http://sqlfiddle.com/#!9/acc5c8/2

Upvotes: 2

Views: 77

Answers (1)

mickmackusa
mickmackusa

Reputation: 47874

I want to recommend wordboundary syntax like this: REGEXP '[[:<:]]demo[[:>:]]'

http://sqlfiddle.com/#!9/d63e05/1

Honestly, if this were my project, I'd be doing a ctype_alnum() check on the search value before bothering to make a trip to the database. However, your requirement is:

only if passing word is alnum or num only using mysql regex

To ensure that the needle string in the query contains at least one number, add another check to the WHERE clause.

More specifically...

SELECT * FROM address WHERE address REGEXP '[[:<:]]demo[[:>:]]' AND 'demo' REGEXP '[0-9]'

This will return no rows as desired.

Upvotes: 1

Related Questions