PostgreSQL remove parts of a split string

Question

I have a column with strings that look like this

ID
-----------------
AB.X.LEFT.01
AB.X.LEFT.06
CD.Y.LEFT.01
CD.Y.RIGHT.01
EF.Z.UP.LEFT.01
EF.Z.DOWN.LEFT.01
GH.I.UP.RIGHT.02
GH.J.LEFT.05

where the strings are delimited by a dot ('.'). I want to remove the last two parts of the string, namely the number (01, 02, ...) and the RIGHT/LEFT statement, or in other words, keep the first 2 OR 3 parts of the string. The resulting column would be

ID
-----------------
AB.X
AB.X
CD.Y
CD.Y
EF.Z.UP
EF.Z.DOWN
GH.I.UP
GH.J

I tried using a combination of split_part(), but the problem is that there is a varying number of parts because of the UP/DOWN possibility so I couldn'd find a solution that properly works. I figured there must be a way to simply split the string and keep all but the last two parts.

Thanks for helping

Pepe N O · Accepted Answer · 2022-12-01 16:28:03Z

3

That can be solved using a regular expression like:

select substring(ID, '(^[A-Z]{2}\.[A-Z]{1}(\.UP|\.DOWN)*)') 
from table1;

Working example here https://www.db-fiddle.com/f/dWZyHZ7aifp44yJBBr8ZPt/1

answered Dec 1, 2022 at 16:28

Pepe N O

2,3841 gold badge12 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL remove parts of a split string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related