Re: char count

[Steve Edberg]

At 12:17 PM -0600 1/21/04, Joseph S Brunzelle wrote:
I'm try to write a query that will return the number of times a specified
character appears in a string (I want known how many times the character "M"
appears), but I cannot seem to figure out how to do that.  The select
statement is the following:
SELECT structure_aa_sequence from structure where apc_id="APC1114";

and this will return the following:

+----------------------------------------------------------------------+
| structure_aa_seq                                                     |
+----------------------------------------------------------------------+
| GGGGGGFVPNWYQHPDPALKYADDMEVYDYYQQYEAAKKAAREASTSSKKTAATSPALPRAKPHVTIA |
+----------------------------------------------------------------------+
Thanks


Well, this query should do it, but I suspect it's too inefficient to 
run on a regular basis:

	select 
length(replace(structure_aa_sequence,'M','MM'))-length(structure_aa_sequence) 
from structure;

I just replace the character of interest with 2 characters (in the 
query; it doesn't affect the database), and check the length 
difference.

To use the counts on a regular basis, it's probably easier to count 
them when you insert or update the record, and store those values in 
a separate column. That's what I ended up doing on a few genetics 
databases I was working on, anyway -

	steve


Joseph S. Brunzelle, Ph.D.
Life Sciences CAT
Dept of Mol. Pharm. and Biol. Chem.
Feinberg School of Medicine
Northwestern University
Phone (630)252-0629  FAX (630)252-0625
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]


--
+------------------------------------------------------------------------+
| Steve Edberg                                      [EMAIL PROTECTED] |
| University of California, Davis                          (530)754-9127 |
| Programming/Database/SysAdmin               http://pgfsun.ucdavis.edu/ |
+------------------------------------------------------------------------+
| [EMAIL PROTECTED]: 1001 Work units on 23 oct 2002                              |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
+------------------------------------------------------------------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]