Group by portion of field

I have a field in a PostgreSQL table, name, with this format:

JOHN^DOE
BILLY^SMITH
FIRL^GREGOIRE
NOEL^JOHN

and so on. The format is LASTNAME^FIRSTNAME. The table has ID, name, birthdate and sex fields. How can I do a SQL statement with GROUP BY FIRSTNAME only ? I have tried several things, and I guess regexp_match could be the way, but I don’t know how to write a correct regular expression for this task. Can you help me ?

Answer

I would recommend split_part():

group by split_part(mycol, '^', 1)

Demo on DB Fiddle:

mycol         | split_part
:------------ | :---------
JOHN^DOE      | JOHN      
BILLY^SMITH   | BILLY     
FIRL^GREGOIRE | FIRL      
NOEL^JOHN     | NOEL

Advertisement

Answer