Using the function SPLIT_PART twice in PostgreSQL?

Question

I have a TEXT column where each text is formatted as such:

/customers/{customer_id}/views/{id1}~{id2}/

I am trying to fetch the id2 only.

My idea is how to split the string by the / character first, where I will have:

customers, {customer_id}, views, {id1}~{id2}.

And then, get the last position:

{id1}~{id2}. And then split it again by the ~ character, and finally get the last position.

The issue is that I am new to SQL and I have no idea if this is even possible. How can I do that and end up with only one column?

SELECT 
  split_part(thetext, '/', 4) as temp
  // how do I proceed from here?
FROM mytable

EDIT: Some examples:

/customers/1231341/views/1312391293~3432491/ /customers/2213441/views/424131~231321341/

The IDs are of different lengths as well.

Answer 1

Use regexp_replace() to capture the part you want while matching the whole input, and replacing (the whole input) with the capture:

select regexp_replace(thetext, '.*~(.*)/', '\1') as temp
from mytable

See live demo.