String to Columns SQL - PostgreSQL-CodePudding

This problem is giving me a headache. I have a column in PostgreSQL that is a string:

Example: bpApx14L103

The characters have always the same structure, the first two letters meaning one thing, etc.

So the output should be into columns:

I can't figure it out, thanks in advance.

CodePudding user response：

You can use the substring() function:

select substring(the_col, 1, 2) as a,
       substring(the_col, 3, 5) as b,
       substring(the_col, 8, 1) as c,
       substring(the_col, 9, 1) as d,
       substring(the_col, 10, 1) as e,
       substring(the_col, 11, 1) as f
from the_table

CodePudding user response：

Another form of substring()

substring(string FROM start_position for length)

SELECT SUBSTRING(column_name FROM 1 FOR 2) a, 
   SUBSTRING(column_name FROM 3 FOR 5) b, 
   SUBSTRING(column_name FROM 8 FOR 1) c, 
   SUBSTRING(column_name FROM 9 FOR 1) d, 
   SUBSTRING(column_name FROM 10 FOR 1) e, 
   SUBSTRING(column_name FROM 11 FOR 1) f 
from your_table name

CodePudding user response：

If you need regex in your substrings you could:

INSERT INTO table_name(A,B,C,D,E,F)
VALUES (
    substring('bpApx14L103' from '^[a-z]{2}'), 
    substring('bpApx14L103', 3, 5) 
    [...]
);

Of course the same works for selects,