Bring text before the second special character in SQL

Question

I have a column that looks like this:

ss pullover jaq:rwbwht:40 
ribbed cttn 5608:navy:10.5 
shorts:black:xl
s:pigment wash mushroom:l  
t tote bag:mushroom:1size

I need to extract the string before the second :, so that it would look like this:

ss pullover jaq:rwbwht
ribbed cttn 5608:navy   
shorts:black  
s:pigment wash mushroom  
t tote bag:mushroom

How do I do that?

I tried RIGHT and substring functions, but they only bring text up to the first : not until the second :.

Answer 1

Another option using regexp:

SELECT REGEXP_SUBSTR(<YOUR_COLUMN>, '^[^:]*:[^:]*') FROM <YOUR_TABLE>;