I have a text looking like this:
>Sequenz: Test 1
MTEITAAMVKELRESTGAGMMDCKNALSETNGDFDKAVQLLREKGLGKAAKKADRLAAEG
>Sequenz 2 1234 Organism: Treponema
MTEITAAMVKELRESTGAGMMDCKNALSETNGDFDKAVQLLREKGLGKAAKKADRLAAEG
>Sequenz 3
MTEITAAMVKELRESTGAGMMDCKNALSETNGDFDKAVQLLREKGLGKAAKKADRLAAEG
There is not necessarily a blank line between the text chunks and it might be a various number of lines of 'MTEITAAMVKELRESTGAGM'. The only thing, which is known for sure is a > before each line.
I want resulting in a table like this:
HEADER
----------
Sequenz: Test 1
----------
Sequenz 2 1234 Organism: Treponema
----------
Sequenz 3
I tried:
SELECT regexp_matches(regexp_split_to_table( 'text from above', '\n>'),'([A-Z,a-z,0-9]+\s)');
resulting in
HEADER
----------
Sequenz
----------
Sequenz
----------
Sequenz
and
Select regexp_split_to_table('text from bove', '[\\\n>+(.)\\\n]+')
resulting in
HEADER
----------
----------
Sequenz: Test 1
----------
MTEITAAMVKELRESTGAGMMDCKNALSETNGDFDKAVQLLREKGLGKAAKKADRLAAEG
----------
----------
Sequenz 2 1234 Organism: Treponema
----------
MTEITAAMVKELRESTGAGMMDCKNALSETNGDFDKAVQLLREKGLGKAAKKADRLAAEG
----------
----------
Sequenz 3
----------
MTEITAAMVKELRESTGAGMMDCKNALSETNGDFDKAVQLLREKGLGKAAKKADRLAAEG
Try this:
SELECT split_part(regexp_split_to_table(trim(leading '>' from '>Sequenz: Test 1
MTEITAAMVKELRESTGAGMMDCKNALSETNGDFDKAVQLLREKGLGKAAKKADRLAAEG
>Sequenz 2 1234 Organism: Treponema
MTEITAAMVKELRESTGAGMMDCKNALSETNGDFDKAVQLLREKGLGKAAKKADRLAAEG
>Sequenz 3
MTEITAAMVKELRESTGAGMMDCKNALSETNGDFDKAVQLLREKGLGKAAKKADRLAAEG'), E'>'),E'\n', 1) AS res
Remove trim() function if you want to keep first empty row.