check for certain characters

2004-05-11 Thread lga2 
hi,
I have a field which is a genome sequence and I need to check if each of 
the entries made for the sequence field contains only a,t,c or g in the string 
and no other characters.
how will i give the query???

thanks,
liz

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

RE: check for certain characters

2004-05-11 Thread Dathan Vance Pattishall 

Off of the top of my head you can basically do a combination of all letters
in big or (use IN) list. It should be pretty fast. I'm personally leaning to
using REGEXP in mySQL yet, that would match the letters in a string and not
exclude others, unless explicitly told to. Using a REGEXP is slow.



--
DVP
 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, May 11, 2004 5:42 PM
 To: [EMAIL PROTECTED]
 Subject: check for certain characters
 
 hi,
 I have a field which is a genome sequence and I need to check if each
 of
 the entries made for the sequence field contains only a,t,c or g in the
 string
 and no other characters.
 how will i give the query???
 
 thanks,
 liz
 
 --
 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: check for certain characters

2004-05-11 Thread Paul DuBois 
At 21:41 -0400 5/11/04, [EMAIL PROTECTED] wrote:
hi,
I have a field which is a genome sequence and I need to check if each of
the entries made for the sequence field contains only a,t,c or g in the string
and no other characters.
how will i give the query???
It depends.  You can find matching values with

WHERE col_name REGEXP '^[atcg]+$'

but you don't say what you want for output, so beyond that it's difficult
to say what the rest of the query should look like.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: check for certain characters

2004-05-11 Thread lga2 
the output of the query should be: all the records that contain even one 
letter other than a,t,c or g.

Liz


Quoting Paul DuBois [EMAIL PROTECTED]:

 At 21:41 -0400 5/11/04, [EMAIL PROTECTED] wrote:
 hi,
  I have a field which is a genome sequence and I need to check if
 each of
 the entries made for the sequence field contains only a,t,c or g in the
 string
 and no other characters.
 how will i give the query???
 
 It depends.  You can find matching values with
 
 WHERE col_name REGEXP '^[atcg]+$'
 
 but you don't say what you want for output, so beyond that it's
 difficult
 to say what the rest of the query should look like.
 
 -- 
 Paul DuBois, MySQL Documentation Team
 Madison, Wisconsin, USA
 MySQL AB, www.mysql.com
 
 -- 
 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: check for certain characters

2004-05-11 Thread Michael Stassen 
Then you could add NOT to Paul's query:

  SELECT * FROM your table WHERE sequence NOT REGEXP '^[atcg]+$';

or, equivalently,

  SELECT * FROM your table WHERE sequence REGEXP '[^atcg]';

I suspect the latter may be faster, but you'd have to try them to be sure.

Note that pattern matching in mysql is case-insensitive by default.   If 
that matters to you, then you would need to add the BINARY keyword to the 
WHERE clause:

  WHERE sequence NOT REGEXP BINARY '^[atcg]+$';

or

  WHERE sequence REGEXP BINARY '[^atcg]';

Michael

[EMAIL PROTECTED] wrote:

the output of the query should be: all the records that contain even one 
letter other than a,t,c or g.

Liz

Quoting Paul DuBois [EMAIL PROTECTED]:


At 21:41 -0400 5/11/04, [EMAIL PROTECTED] wrote:

hi,
   I have a field which is a genome sequence and I need to check if
each of

the entries made for the sequence field contains only a,t,c or g in the
string

and no other characters.
how will i give the query???
It depends.  You can find matching values with

WHERE col_name REGEXP '^[atcg]+$'

but you don't say what you want for output, so beyond that it's
difficult
to say what the rest of the query should look like.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
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: check for certain characters

2004-05-11 Thread Robert A. Rosenberg 
At 23:51 -0400 on 05/11/2004, Michael Stassen wrote about Re: check 
for certain characters:

Then you could add NOT to Paul's query:

  SELECT * FROM your table WHERE sequence NOT REGEXP '^[atcg]+$';

or, equivalently,

  SELECT * FROM your table WHERE sequence REGEXP '[^atcg]';

I suspect the latter may be faster, but you'd have to try them to be sure.

Note that pattern matching in mysql is case-insensitive by default. 
If that matters to you, then you would need to add the BINARY 
keyword to the WHERE clause:

  WHERE sequence NOT REGEXP BINARY '^[atcg]+$';

or

  WHERE sequence REGEXP BINARY '[^atcg]';

Michael
The need to go BINARY to detect case also requires that sequence be a 
BLOB not a TEXT field (I might have the 
case-sensitive/case-insensitive types reversed),

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