How to split a string and select nth part

Question

I want to split a string using a colon as delimiter and select 2nd and 3rd part.

Table:

|asdsc:dcdes:dcd|
|dcfd:drfe:regf |

Expected output:

|dcdes|
|drfe|

and also third column:

|dcd |
|regf|

Answer 1

Use split_part():

with my_table(str) as (
    values 
    ('asdsc:dcdes:dcd'),
    ('dcfd:drfe:regf')
)

select 
    str, 
    split_part(str, ':', 2) as part_no_2, 
    split_part(str, ':', 3) as part_no_3
from my_table

       str       | part_no_2 | part_no_3 
-----------------+-----------+-----------
 asdsc:dcdes:dcd | dcdes     | dcd
 dcfd:drfe:regf  | drfe      | regf
(2 rows)    

Answer 2

You could use string_to_array to convert your string to array using : as the delimiter to get each element between the delimiter and then specify index of your array to retrieve values. Here you want to omit first, but take second and third.

Code:

select strarr[2], strarr[3]
from (
select string_to_array(string_col, ':') as strarr
from yourtable
  ) t