Re: query question using REGEXP

[Michael Stassen]

You could also do this with REGEXP, using [[:<:]] and [[:>:]] which are 
character classes that match word boundaries, including comma, beginning 
of line, and end of line.  Then finding rows which include 2, for 
example, would look something like this:

  SELECT * FROM yourtable WHERE column REGEXP "[[:<:]]2[[:>:]]";

This may be faster than the CONCAT/LIKE version, as it compares the 
actual column value rather than a function of the column value.

That said, it might be a good idea to consider alternative ways to store 
your data.  As it stands, it appears you are trying to store multiple 
(numeric) values in a single (char) column, which usually isn't a good 
idea.

If you have a fixed set of numbers which show up in your numbers list 
column, and if there are 64 or fewer of them, you may wish to consider 
the SET type <http://www.mysql.com/doc/en/SET.html>.

Another option is to store one number value per row.  Your table would 
then look something like

  Record     Column
  1          12
  2          1
  2          2
  2          5
  2          6
  3          1
  3          12
  3          24
  3          45
  4          2
  4          6
Then finding records with a value of 2, for example, becomes simply

  SELECT * FROM yourtable WHERE column = 2;

This is likely to be the fastest, as this query could take advantage of 
an index on column.

Michael

Matt W wrote:

Hi Anthony,

You don't need REGEXP for this; LIKE will do.  Try something like this:

... WHERE CONCAT(',', Column, ',') LIKE '%,2,%'

to search for rows that contain 2.

Hope that helps.

Matt

----- Original Message -----
From: "award"
Sent: Saturday, March 13, 2004 2:16 PM
Subject: query question using REGEXP


Hi,

I'm storing in a database  numbers separated by comma if more than one
number i.e
Record #       Column
1                                        12
2                                        1,2,5,6
3                                        1,12,24,45
4                                         2,6
Now if I want to search for records that has number 1 it will find
records 1,2,3
but what I want to return only record 2,3
an example is that if I'm looking for a record that has a number 2
it will print record 1,2,3,4 but what I want is only record 2,4
Any help is appreciated,
anthony





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]