Re: query question using REGEXP
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
112
21,2,5,6
31,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]
query question using REGEXP
Hi, I'm storing in a database numbers separated by comma if more than one number i.e Record # Column 112 21,2,5,6 31,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
Re: query question using REGEXP
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
112
21,2,5,6
31,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]
Re: query question using REGEXP
Hi Anthony,
Please reply to the list also (and not just me) so others can follow the
discussion. :-)
Well, if the commas are sometimes there, sometimes not (*with multiple
numbers*), that's a problem. However, if you just mean that the commas
aren't there when it's just one number, then the query I gave will work
fine for that because it adds a comma to the beginning and end of the
column (with CONCAT()) before doing the LIKE comparison.
Matt
- Original Message -
From: award
Sent: Saturday, March 13, 2004 5:10 PM
Subject: RE: query question using REGEXP
Hi thanks for the help
But the problem in the column it can take various form
Just as
1
1,2
12
1,22,4
sometimes I have the comma and sometimes I do not have them.
So if do WHERE column LIKE %2%
would it work??
thank you
anthony
-Original Message-
From: Matt W [mailto:[EMAIL PROTECTED]
Sent: 13 March 2004 22:47
To: award; [EMAIL PROTECTED]
Subject: Re: query question using REGEXP
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
112
21,2,5,6
31,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]
