List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:January 21 2004 7:09pm
Subject:Re: char count
View as plain text  
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
>j-brunzelle@stripped
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


-- 
+------------------------------------------------------------------------+
| Steve Edberg                                      sbedberg@stripped |
| University of California, Davis                          (530)754-9127 |
| Programming/Database/SysAdmin               http://pgfsun.ucdavis.edu/ |
+------------------------------------------------------------------------+
| SETI@Home: 1001 Work units on 23 oct 2002                              |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
+------------------------------------------------------------------------+
Thread
char countJoseph S Brunzelle21 Jan
  • Re: char countSteve Edberg21 Jan
    • Re: char countDan Nelson21 Jan