PostgreSQL remove parts of a split string-CodePudding

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

CodePudding user response：

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