partial string extraction with sql using regexp ?
hi all,
Here's a nut to crack:
I would like to extract a part of a string stored in a varchar column in a
mysql table.
Since the part of string I want to extract is neither at a fixed position
nor logically separated by a fixed character I was thinking using regexp
would by a good idea
Sample data:
alf eats 2 cats
peter's 50 cards
apple ibook 2345 sold by apple computers
scandlines mariner vessel 345 operated by Nordic Transport
(all these are made up)
What I want to extract is the part of string that is the part that comes
before the first digit ...
using perl i would :
if ($string =~ /^(.*)\d+/) {$wanted = $1;}
now my question :
if testing in sql for :
column1 regexp '([[:alpha:]]+)[[:digit:]]+([[:alpha:]]+)'
how then can I extract / refer to the first subpattern ([[:alpha:]]+) ?
What I would want is something like (what obviously doesnt work since @1 is
not set):
select
if (
column1 regexp '([[:alpha:]]+)[[:digit:]]+([[:alpha:]]+)',
@1,
column1
)
Besides I m running Mysql 5.0.23
Any suggestios welcome
TIA
CVH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
partial string extraction with sql using regexp ?
hi all,
Here's a nut to crack:
I would like to extract a part of a string stored in a varchar column in a
mysql table.
Since the part of string I want to extract is neither at a fixed position
nor logically separated by a fixed character I was thinking using regexp
would by a good idea
Sample data:
alf eats 2 cats
peter's 50 cards
apple ibook 2345 sold by apple computers
scandlines mariner vessel 345 operated by Nordic Transport
(all these are made up)
What I want to extract is the part of string that is the part that comes
before the first digit ...
using perl i would :
if ($string =~ /^(.*)\d+/) {$wanted = $1;}
now my question :
if testing in sql for :
column1 regexp '([[:alpha:]]+)[[:digit:]]+([[:alpha:]]+)'
how then can I extract / refer to the first subpattern ([[:alpha:]]+) ?
What I would want is something like (what obviously doesnt work since @1 is
not set):
select
if (
column1 regexp '([[:alpha:]]+)[[:digit:]]+([[:alpha:]]+)',
@1,
column1
)
Besides I m running Mysql 5.0.23
Any suggestios welcome
TIA
CVH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: partial string extraction with sql using regexp ?
I'ld like to accomplish something like: set @myvar=concat_ws(,,(SELECT column from table1 order by column; where ...)) or select concat_ws(,,(SELECT column from table1 order by column where ...)); for further usage in sql-scripts I forgot to mention: As I need it in a function, i think i cant use the second version. Does anyone know of a way to build a comma-separated list aka set from rows of a table? Any suggestions welcome! TIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
On 30 Jun 2004 17:45:06 +0200, wrote: In article [EMAIL PROTECTED], SGreen writes: SELECT t1.* FROM ytbl_development t1 INNER JOIN tmpShortCodes sc ON INSTR(t1.txtDevPostCode, sc.short_code) =1 This is the same as SELECT t1.* FROM ytbl_development t1 INNER JOIN tmpShortCodes sc ON t1.txtDevPostCode LIKE concat(sc.short_code, '%') and this query would use indexes on txtDevPostCode and short_code. Thnx to all for this mysql primer, will let you know how it goes, but have been dragged off to another crisis zzapper (vim, cygwin, wiki zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
Michael
Ignoring my attempt at a query, I'll restate the problem
T1.devtxtpostcode contains full UK Postcodes eg OX14 5RA, OX14 5BH, Se1 1AH, etc
I want to check if a particular postcode is within a list of postcode areas, these
postcode areas
are naturally shorter ie ox14 5,ox14 6 etc. So I need to write a query that will check
if OX14 5RA
matches one of the postcode areas
If UK Postcodes had a fixed structure I could write
select * from ytbl_development as t1
where mid(t1.txtDevPostCode,1,5) in ('ox14 1','ox14 2','se1 1')
unfortunately I can't use mid as I can't guarantee that the length of a short postcode
is 5 chars
How would you solve this problem
(The list of short Area Postcodes is generated by an earlier query)
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
I think I understand the problem now... You generate a list of postal
prefixes (the first portion of a full postal code) whose items may or may
not be all the same length. Then you could want to do either of two things:
1) compare a given full postal code to the list to see if matches any of
the short codes (the prefixes)
- or -
2) Scan a list of all full codes to see which ones are covered by your
short codes
Bad news: In either case, you will most likely _not_ be able to use an
index in the search
Good news: this is a solvable problem
Instead of converting your previous query (the one that generates the
short codes) into a comma delimited list, I would put that list into a
temporary table
CREATE TEMPORARY TABLE tmpShortCodes
SELECT short_code
FROM (--- this is your query that creates your short code
list.)
Then we can do a bulk comparison of the columns in tmpShortCodes to one or
many full codes. What makes this simpler to achieve is the fact that you
need to match only the beginning characters of the full code to an entire
short code. MySQL has 2 nearly identical functions for this: INSTR() and
LOCATE()
SELECT t1.*
FROM ytbl_development t1
INNER JOIN tmpShortCodes sc
ON INSTR(t1.txtDevPostCode, sc.short_code) =1
Like I said, it won't be fast but it should find the matches.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
zzapper
[EMAIL PROTECTED]To: [EMAIL PROTECTED]
cc:
Sent by: newsFax to:
[EMAIL PROTECTED]Subject: Re: Using REGEXP
rg
06/30/2004 04:31
AM
Michael
Ignoring my attempt at a query, I'll restate the problem
T1.devtxtpostcode contains full UK Postcodes eg OX14 5RA, OX14 5BH, Se1
1AH, etc
I want to check if a particular postcode is within a list of postcode
areas, these postcode areas
are naturally shorter ie ox14 5,ox14 6 etc. So I need to write a query that
will check if OX14 5RA
matches one of the postcode areas
If UK Postcodes had a fixed structure I could write
select * from ytbl_development as t1
where mid(t1.txtDevPostCode,1,5) in ('ox14 1','ox14 2','se1 1')
unfortunately I can't use mid as I can't guarantee that the length of a
short postcode is 5 chars
How would you solve this problem
(The list of short Area Postcodes is generated by an earlier query)
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
In all of your examples so far, the short postcode ends with the first
character after the space. If that is true for all short postcodes, we
could take the portion of the full postcode up to the first character after
the space, then compare that to the list. I think that's what you were
hoping to do with the regexp. Since your list is comma-separated, we can
use FIND_IN_SET to compare the portion of the postcode to the list. So,
SELECT * FROM ytbl_development AS t1
WHERE FIND_IN_SET(LEFT(t1.txtDevPostCode,LOCATE(' ',t1.txtDevPostCode)+1),
'OX14 1','OX14 2','SE1 1');
This won't use an index on txtDevPostCode, so it will require a full table scan.
Michael
zzapper wrote:
Michael
Ignoring my attempt at a query, I'll restate the problem
T1.devtxtpostcode contains full UK Postcodes eg OX14 5RA, OX14 5BH, Se1 1AH, etc
I want to check if a particular postcode is within a list of postcode areas, these postcode areas
are naturally shorter ie ox14 5,ox14 6 etc. So I need to write a query that will check if OX14 5RA
matches one of the postcode areas
If UK Postcodes had a fixed structure I could write
select * from ytbl_development as t1
where mid(t1.txtDevPostCode,1,5) in ('ox14 1','ox14 2','se1 1')
unfortunately I can't use mid as I can't guarantee that the length of a short postcode
is 5 chars
How would you solve this problem
(The list of short Area Postcodes is generated by an earlier query)
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: SELECT t1.* FROM ytbl_development t1 INNER JOIN tmpShortCodes sc ON INSTR(t1.txtDevPostCode, sc.short_code) =1 This is the same as SELECT t1.* FROM ytbl_development t1 INNER JOIN tmpShortCodes sc ON t1.txtDevPostCode LIKE concat(sc.short_code, '%') and this query would use indexes on txtDevPostCode and short_code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
Harold, THANK YOU!! As I was writing that bit of code I had that creepy feeling that knew that I was overlooking something simple. I guess I win the D'OH prize for today. 8-D. (Maybe I shouldn't write any more SQL until *after* the coffee kicks in..hmmm...) Nice catch! Shawn Harald Fuchs [EMAIL PROTECTED]To: [EMAIL PROTECTED] .netcc: Sent by: newsFax to: [EMAIL PROTECTED]Subject: Re: Using REGEXP rg 06/30/2004 11:45 AM Please respond to hf517 In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: SELECT t1.* FROM ytbl_development t1 INNER JOIN tmpShortCodes sc ON INSTR(t1.txtDevPostCode, sc.short_code) =1 This is the same as SELECT t1.* FROM ytbl_development t1 INNER JOIN tmpShortCodes sc ON t1.txtDevPostCode LIKE concat(sc.short_code, '%') and this query would use indexes on txtDevPostCode and short_code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using REGEXP
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP ^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes OX14 1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
MySql 4.018
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using REGEXP
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP ^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes OX14 1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
MySql 4.018
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
A quick review of the REGEXP portion of the manual helped me to understand
what went wrong:
http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html
REGEXP is a comparitor, not a function. It works like = or and the
result is a boolean value.
Were you trying to validate t1.postcode (to make sure it fit a certain
style of postal codes, as defined in your regular expression) AND make
sure it was one of a list of codes given to you by the user?
If you were, you needed to create your WHERE clause this way:
WHERE t1.postcode REGEXP
^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1}
AND t1.postcode in (insert your comma delimited list here)
The rule is: if you need to compare the same value against two conditions,
you have to make two distinct comparisons.
Cheers!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
zzapper
[EMAIL PROTECTED]To: [EMAIL PROTECTED]
cc:
Sent by: newsFax to:
[EMAIL PROTECTED]Subject: Using REGEXP
rg
06/29/2004 09:25
AM
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP
^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use REGEXP
this way
qryRadius.shortpostcode contains a list of short postcodes OX14 1,OX14 2
etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
MySql 4.018
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
zzapper:
I could be reading it wrong, but it looks like you're looking for the
result of your REGEXP in a list. REGEXP returns only a 0 or 1, not the
expression resulting from performing a REGEXP.
Wes
On Jun 29, 2004, at 9:25 AM, zzapper wrote:
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP
^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use
REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes OX14
1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
MySql 4.018
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
On Tue, 29 Jun 2004 15:13:10 -0400, wrote:
zzapper:
I could be reading it wrong, but it looks like you're looking for the
result of your REGEXP in a list. REGEXP returns only a 0 or 1, not the
expression resulting from performing a REGEXP.
Wes
On Jun 29, 2004, at 9:25 AM, zzapper wrote:
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP
^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use
REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes OX14
1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
Shawn,
Your solution doesn't work unfortunately because I need to operate on t1.postcode
before making the
comparison (IT'S USUALLY the OTHER WAY ROUND) , I think this must be a generic
problem, so I think
an eventual solution will be interesting
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
Then you need to tell us what operation needs to be performed on t1.postcode
before making the comparison. That is, describe what you want, rather than
what didn't work.
Michael
zzapper wrote:
On Tue, 29 Jun 2004 15:13:10 -0400, wrote:
zzapper:
I could be reading it wrong, but it looks like you're looking for the
result of your REGEXP in a list. REGEXP returns only a 0 or 1, not the
expression resulting from performing a REGEXP.
Wes
On Jun 29, 2004, at 9:25 AM, zzapper wrote:
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP
^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use
REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes OX14
1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
Shawn,
Your solution doesn't work unfortunately because I need to operate on t1.postcode
before making the
comparison (IT'S USUALLY the OTHER WAY ROUND) , I think this must be a generic
problem, so I think
an eventual solution will be interesting
zzapper (vim, cygwin, wiki zsh)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
I'm sorry for my overly terse reply.
Perhaps I'm being dense, but I just don't get it. Your REGEXP matches a
string which starts with 1 or 2 letters, followed by 1 or 2 digits, followed
by 0 or 1 letters, which tells me a short postcode would be 'OX14' or
'OX14A', but your example short postcodes are 'OX14 1' and 'OX14 2'. You
add that the shortpostcodes column contains a list, but don't explicitly
state what it looks like. I'd guess 'OX14 1,OX14 2', but then you throw me
off with like '%xx%' would work if it weren't for the list.
Perhaps this would all be obvious to me if I were familiar with UK
postcodes, but I'm not, so I decided to ask for clarification rather than
guess. If you would clarify how to divide the short part from a postcode,
and verify what the list looks like, I'd be happy to try to come up with a
solution.
Michael
David Rayner wrote:
Michael,
I do believe I have described what I need doing to t1.txtDevPostCode (see
below) I need to truncate the t1.txtDevPostCode according to the RegExp
(UK Postcodes do not unfortunately have fixed lengths) and then compare
it with a list of already truncated Postcodes ie OX14 1, OX14 2 etc
If I was just comparing with a single value rather than a list I could do
a like '%xx%'
From: Michael Stassen [EMAIL PROTECTED]
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP
^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use
REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes
OX14 1,OX14 2 etc whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
Shawn,
Your solution doesn't work unfortunately because I need to operate on t1.postcode
before making the
comparison (IT'S USUALLY the OTHER WAY ROUND) , I think this must be a generic
problem, so I think
an eventual solution will be interesting
zzapper (vim, cygwin, wiki zsh)
--
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
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]
select using regexp
Hi, I've got a problem that I'm hoping someone can help with. I need to do a query against a text column in order to extract entries that will match whole words only - for example, a search for cat should match any of: The cat sat on the mat It was a large cat. Cat food is interesting. Dog. Cat. Fish. but not match in a catatonic state it was a catastrophe scattergun approach It looks as if the MySQL REGEXP function is what I need here, but I can't work out from the documentation how to get what I want. Any suggestions? Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select using regexp
Mark, It looks like you should be using full-text indexes and the match and against functions to me. Check out section 6.8 in the manual. Andy mysql query -Original Message- From: Mark Goodge [mailto:mark;good-stuff.co.uk] Sent: 04 November 2002 11:21 To: [EMAIL PROTECTED] Subject: select using regexp Hi, I've got a problem that I'm hoping someone can help with. I need to do a query against a text column in order to extract entries that will match whole words only - for example, a search for cat should match any of: The cat sat on the mat It was a large cat. Cat food is interesting. Dog. Cat. Fish. but not match in a catatonic state it was a catastrophe scattergun approach It looks as if the MySQL REGEXP function is what I need here, but I can't work out from the documentation how to get what I want. Any suggestions? Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select using regexp
Hi
You could use either normal or fulltext searches. Regexp may well be a good
answer (not used it myself). The following should also work
SELECT *
FROM table
WHERE
field LIKE % cat %
OR field LIKE % cat. %
OR field LIKE % cat, %
(note the spaces to make sure you get only complete words)
or if you have a mysql version that supports FULLTEXT
SELECT * FROM table
WHERE MATCH (field) AGAINST ('cat');
http://www.mysql.com/doc/en/Fulltext_Search.html
which is much more elegant
HTH
Peter
---
Excellence in internet and open source software
---
Sunmaia
Birmingham
UK
www.sunmaia.net
tel. 0121-242-1473
International +44-121-242-1473
---
-Original Message-
From: Mark Goodge [mailto:mark;good-stuff.co.uk]
Sent: 04 November 2002 11:21
To: [EMAIL PROTECTED]
Subject: select using regexp
Hi,
I've got a problem that I'm hoping someone can help with. I need to do
a query against a text column in order to extract entries that will
match whole words only - for example, a search for cat should match
any of:
The cat sat on the mat
It was a large cat.
Cat food is interesting.
Dog. Cat. Fish.
but not match
in a catatonic state
it was a catastrophe
scattergun approach
It looks as if the MySQL REGEXP function is what I need here, but I
can't work out from the documentation how to get what I want.
Any suggestions?
Mark
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select using regexp
There are too many exceptions for this to be usefull.
What about lines ending in cat. or cat,
Your example won't match them.
Perhaps % cat.% and % cat,% patterns might be more helpfull,
but what about lines that begin with cat?
Peter Lovatt wrote:
Hi
You could use either normal or fulltext searches. Regexp may well be a good
answer (not used it myself). The following should also work
SELECT *
FROM table
WHERE
field LIKE % cat %
OR field LIKE % cat. %
OR field LIKE % cat, %
(note the spaces to make sure you get only complete words)
or if you have a mysql version that supports FULLTEXT
SELECT * FROM table
WHERE MATCH (field) AGAINST ('cat');
http://www.mysql.com/doc/en/Fulltext_Search.html
which is much more elegant
HTH
Peter
---
Excellence in internet and open source software
---
Sunmaia
Birmingham
UK
www.sunmaia.net
tel. 0121-242-1473
International +44-121-242-1473
---
-Original Message-
From: Mark Goodge [mailto:mark;good-stuff.co.uk]
Sent: 04 November 2002 11:21
To: [EMAIL PROTECTED]
Subject: select using regexp
Hi,
I've got a problem that I'm hoping someone can help with. I need to do
a query against a text column in order to extract entries that will
match whole words only - for example, a search for cat should match
any of:
The cat sat on the mat
It was a large cat.
Cat food is interesting.
Dog. Cat. Fish.
but not match
in a catatonic state
it was a catastrophe
scattergun approach
It looks as if the MySQL REGEXP function is what I need here, but I
can't work out from the documentation how to get what I want.
Any suggestions?
Mark
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select using regexp
Hi,
REGEXP is much more powerful than LIKE; you can match full words
with this syntax:
SELECT *
FROM TABLE
WHERE field REGEXP [[::]]cat[[::]];
(Easy, isn't it ? ;) )
You can find more examples in the manual:
http://www.mysql.com/doc/en/Regexp.html
Regards,
Joseph Bueno
NetClub
gerald_clark wrote:
There are too many exceptions for this to be usefull.
What about lines ending in cat. or cat,
Your example won't match them.
Perhaps % cat.% and % cat,% patterns might be more helpfull,
but what about lines that begin with cat?
Peter Lovatt wrote:
Hi
You could use either normal or fulltext searches. Regexp may well be a
good
answer (not used it myself). The following should also work
SELECT *
FROM table
WHERE
field LIKE % cat %
OR field LIKE % cat. %
OR field LIKE % cat, %
(note the spaces to make sure you get only complete words)
or if you have a mysql version that supports FULLTEXT
SELECT * FROM table
WHERE MATCH (field) AGAINST ('cat');
http://www.mysql.com/doc/en/Fulltext_Search.html
which is much more elegant
HTH
Peter
---
Excellence in internet and open source software
---
Sunmaia
Birmingham
UK
www.sunmaia.net
tel. 0121-242-1473
International +44-121-242-1473
---
-Original Message-
From: Mark Goodge [mailto:mark;good-stuff.co.uk]
Sent: 04 November 2002 11:21
To: [EMAIL PROTECTED]
Subject: select using regexp
Hi,
I've got a problem that I'm hoping someone can help with. I need to do
a query against a text column in order to extract entries that will
match whole words only - for example, a search for cat should match
any of:
The cat sat on the mat
It was a large cat.
Cat food is interesting.
Dog. Cat. Fish.
but not match
in a catatonic state
it was a catastrophe
scattergun approach
It looks as if the MySQL REGEXP function is what I need here, but I
can't work out from the documentation how to get what I want.
Any suggestions?
Mark
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
