How to search a pattern in MySQL ?

[pankajit09]

Hi all of you !!

I have a column in a table which has data seperated by spaces like this -->
AS DD QW-02-44 WE
FG QW-02-44 RE WE
QW WQ

I am implementing a search functionality wherein a user will enter some data in a textbox to search.
For eg:- if the user wants to retreive rows which have WQ in them then the result should be 3rd row above.

How to do this ?

Platform --> Debian Sarge
Language --> Perl CGI

[ronverdonk]

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).

$search = 'WQ';
$sql1 = 'SELECT * FROM table ';
$sql1.= 'WHERE column REGEXP "' . $search . '"';
 
or  
 
$sql2 = 'SELECT * FROM table ';
$sql2.= 'WHERE column LIKE "%' . $search . '%"';
 

Ronald :cool:

[pankajit09]

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).

$search = 'WQ';
$sql1 = 'SELECT * FROM table ';
$sql1.= 'WHERE column REGEXP "' . $search . '"';
 
or  
 
$sql2 = 'SELECT * FROM table ';
$sql2.= 'WHERE column LIKE "%' . $search . '%"';
 

Ronald :cool:

Suppose I want to search WQ QW(random) then also I want the 3rd row .
How to do that ?

[ronverdonk]

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

$search1 = 'WQ';
$search2 = 'QW';
$search3 = 'WQ QW';
 
// search for 1 string 'WQ QW' 
$sqla  = 'SELECT * from table ';
$sqla .= 'WHERE column REGEXP "' . search3 . '" ';
 
// search for 2 strings 'WQ' and 'QW' 
$sqlb  = 'SELECT * from table ';
$sqlb .= 'WHERE column REGEXP "' . search1 . '" ';
$sqlb .= 'AND column REGEXP "' . $search2 . '" ';
 

Ronald :cool:

[pankajit09]

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

$search1 = 'WQ';
$search2 = 'QW';
$search3 = 'WQ QW';
 
// search for 1 string 'WQ QW' 
$sqla  = 'SELECT * from table ';
$sqla .= 'WHERE column REGEXP "' . search3 . '" ';
 
// search for 2 strings 'WQ' and 'QW' 
$sqlb  = 'SELECT * from table ';
$sqlb .= 'WHERE column REGEXP "' . search1 . '" ';
$sqlb .= 'AND column REGEXP "' . $search2 . '" ';
 

Ronald :cool:

Thanks Ronald for that.
This is what I wanted.

I like the smiley beside your name . :-D

[ronverdonk]

Glad I could help you out.

Ronald :cool:

[pankajit09]

One more problem -->

*europe should match "DD EASTEUROPE" and not
"WE EUROPE" (ignore spaces).

How to do this ?

[pankajit09]

ok no need to think I got -->

$textbox =~ s/\s/[^[:space:]]/g;

$sqla = 'SELECT * from table ';
$sqla .= 'WHERE column REGEXP "$textbox" ';

[viswanatareddy]

Hi Friends,
i am having one trigger with all insert ,update and delete opreations, then how to call this trigger from user form.

environment is :
ASP.NET with C#
Sql Server

kindly help me inthis regard.

thanks
viswanath.

[ronverdonk]

viswanatareddy:

You are

(a) in the wrong thread (this thread has nothing to do with triggers).
(b) in the wrong forum (post your question in SQL server or .NET forum)

Ronald :cool:

[pankajit09]

Ronald,

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

[ronverdonk]

Ronald,

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

The main reason why I prefer a REGEXP above the LIKE is that it gives more flexibility as opposed to a more rigid matching with LIKE. These reasons are (MySQL doc):

• 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)

See also the MySQL documentation on Regular Expressions

Ronald :cool: