How to search a pattern in MySQL ?

There are different ways of doing this, such as using the LIKE attrib or a regular expression. The following samples show each method (I personnaly favor the regular expression method).

Expand|Select|Wrap|Line Numbers

1. $search = 'WQ';
2. $sql1 = 'SELECT * FROM table ';
3. $sql1.= 'WHERE column REGEXP "' . $search . '"';
4.  
5. or  
6.  
7. $sql2 = 'SELECT * FROM table ';
8. $sql2.= 'WHERE column LIKE "%' . $search . '%"';
9.  

Ronald :cool:

Depends on whether you want to test for one string 'WQ QW' or for 2 separate strings 'WQ' and 'QW'.

Expand|Select|Wrap|Line Numbers

1. $search1 = 'WQ';
2. $search2 = 'QW';
3. $search3 = 'WQ QW';
4.  
5. // search for 1 string 'WQ QW' 
6. $sqla  = 'SELECT * from table ';
7. $sqla .= 'WHERE column REGEXP "' . search3 . '" ';
8.  
9. // search for 2 strings 'WQ' and 'QW' 
10. $sqlb  = 'SELECT * from table ';
11. $sqlb .= 'WHERE column REGEXP "' . search1 . '" ';
12. $sqlb .= 'AND column REGEXP "' . $search2 . '" ';
13.  

Ronald :cool:

Ronald,

may i know why you personally favour the regular expression method ?

• classes: in my opinion the most important one. A character class ‘[...]’ matches any character within the brackets.
  For example, ‘[abc]’ matches ‘a’, ‘b’, or ‘c’. To name a range of characters, use a dash. ‘[a-z]’ matches any letter, whereas ‘[0-9]’ matches any digit.

• no of instances: Also important to me. ‘*’ matches zero or more instances of the thing preceding it.
  For example, ‘x*’ matches any number of ‘x’ characters, ‘[0-9]*’ matches any number of digits, and ‘.*’ matches any number of anything.

• position: a REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern)