469,127 Members | 1,413 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,127 developers. It's quick & easy.

How to search a pattern in MySQL ?

296 100+
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
Dec 26 '06 #1
11 3580
ronverdonk
4,258 Expert 4TB
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:
Dec 26 '06 #2
pankajit09
296 100+
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:

Suppose I want to search WQ QW(random) then also I want the 3rd row .
How to do that ?
Dec 26 '06 #3
ronverdonk
4,258 Expert 4TB
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:
Dec 26 '06 #4
pankajit09
296 100+
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:

Thanks Ronald for that.
This is what I wanted.

I like the smiley beside your name . :-D
Dec 27 '06 #5
ronverdonk
4,258 Expert 4TB
Glad I could help you out.

Ronald :cool:
Dec 27 '06 #6
pankajit09
296 100+
One more problem -->

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

How to do this ?
Dec 30 '06 #7
pankajit09
296 100+
ok no need to think I got -->

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

$sqla = 'SELECT * from table ';
$sqla .= 'WHERE column REGEXP "$textbox" ';
Dec 30 '06 #8
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.
Dec 30 '06 #9
ronverdonk
4,258 Expert 4TB
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:
Dec 30 '06 #10
pankajit09
296 100+
Ronald,

may i know why you personally favour the regular expression method ?
Jan 2 '07 #11
ronverdonk
4,258 Expert 4TB
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:
Jan 2 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Sharif T. Karim | last post: by
5 posts views Thread by Vamsee Krishna Gomatam | last post: by
3 posts views Thread by Andrew Crowe | last post: by
22 posts views Thread by Phlip | last post: by
2 posts views Thread by Alphonse Giambrone | last post: by
3 posts views Thread by FluffyCat | last post: by
1 post views Thread by Eric | last post: by
4 posts views Thread by | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.