Difference between revisions of "MySQL/scripts"
From Christoph's Personal Wiki
| Line 4: | Line 4: | ||
WHERE kingdom!='Bacteria' | WHERE kingdom!='Bacteria' | ||
GROUP BY accession HAVING n>2; | GROUP BY accession HAVING n>2; | ||
| + | |||
| + | *Padding integers | ||
| + | Say you have a series of integers for your <code>id</code> column in a table and you wish to pad them with zeros if they are less than 1000 (i.e., "<code>1</code>" -> "<code>001</code>"), then: | ||
| + | SELECT RIGHT(1000 + id,3) AS id FROM mydb; | ||
Revision as of 23:35, 23 July 2007
- Find duplicate entries
SELECT accession,count(*) AS n FROM mydb WHERE kingdom!='Bacteria' GROUP BY accession HAVING n>2;
- Padding integers
Say you have a series of integers for your id column in a table and you wish to pad them with zeros if they are less than 1000 (i.e., "1" -> "001"), then:
SELECT RIGHT(1000 + id,3) AS id FROM mydb;
