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.
Advertisement
Answer
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.