Hi everyone,
I have sequences stored in an SQL database, with the header stored in one field and the sequence (single line) in another.
header1 GACTACGACT...
header2 ATACGATCAT...
I've been exporting sequences as a csv and running another process to put them in proper fasta format. Can anyone suggest an SQL query that would pull them out in fasta format directly, also adding the ">" to the header? Some students are also using the data and they are still learning the basics of unix commands.
Any advice will be appreciated.
Stacy
1 answer
say you're using sqlite3:
$ sqlite3 db.sqlite3
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table fasta(name,sequence);
sqlite> insert into fasta(name,sequence) values ("abcd","ATGACAGATCGATCGATGTACGTAGCTGCTAGTGATATGCTAGCTGCTGATAGCTAGTAGCTAGC");
sqlite> insert into fasta(name,sequence) values ("efegh","ATGATCGTATGGGATCGAGTCTAAAAAAA");
sqlite> (ctrl-d)
export ('||' is the concatenation operator and x'0A' is the carriage return). Another sql engine would have a different syntax(mysql: 'concat') but the idea is the same.
$ sqlite3 db.sqlite3 "select ('>' || name || x'0A' || sequence) from fasta;"
>abcd
ATGACAGATCGATCGATGTACGTAGCTGCTAGTGATATGCTAGCTGCTGATAGCTAGTAGCTAGC
>efegh
ATGATCGTATGGGATCGAGTCTAAAAAAA
Log in to answer this question.
what is your SQL engine ?
Thank you very much!