PostgreSQL, splitting to rows and recognizing the first part

Question

I have a table in PostgreSQL:

CREATE TABLE t1 (
id SERIAL, 
values TEXT);

INSERT INTO t1 (values) 
VALUES ('T815,T847'), ('F00,B4R,B4Z'), ('AS5,XX3'), ('G00');

like:

id|values
--------------
1 |T815,T847
2 |F00,B4R,B4Z
3 |AS5,XX3
4 |G00

I need to split the values in the values column on their own row and mark the first values, like this:

id|first|value
--------------
1 | yes | T815
1 |     | T84T
2 | yes | F00
2 |     | B4R
2 |     | B4Z
3 | yes | AS5
3 |     | XX3
4 | yes | G00

I can produce id and value with:

SELECT t1.id, 
       regexp_split_to_table(t1.values, E',') 
FROM t1;

but how would I recognize and tag the first values?

Gordon Linoff · Accepted Answer · 2014-10-21 10:42:37Z

3

There may be a better way, but you can use a brute force method:

SELECT t1.id, value,
       (case when values like value || '%' then 'yes' end) as first
FROM (SELECT t1.id,  t1.values,
             regexp_split_to_table(t1.values, E',') as value
      FROM t1
     ) t1;

answered Oct 21, 2014 at 10:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Brown Over a year ago

It's fine, thanks. Now reading your solution I realize I should've used names other than t1, value or values...

Collectives™ on Stack Overflow

PostgreSQL, splitting to rows and recognizing the first part

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related