Extract text after Nth occurance

Question

I have several strings like this:

1:723:NVDA:NSDQ::Z4189274321:Snapshot Report:10:COMPUTER & TECHNOLOGY:241:Semiconductor

I need to return everything after the second colon (:). So, from the above string I need:

NVDA:NSDQ::Z4189274321:Snapshot Report:10:COMPUTER & TECHNOLOGY:241:Semiconductor

Seeing lots of examples, but none are really working for this task.

Answer 1

select array_to_string((string_to_array('1:723:NVDA:NSDQ::Z4189274321:Snapshot Report:10:COMPUTER & TECHNOLOGY:241:Semiconductor', ':'))[3:], ':');

NVDA:NSDQ::Z4189274321:Snapshot Report:10:COMPUTER & TECHNOLOGY:241:Semiconductor

This uses string_to_array from here:

https://www.postgresql.org/docs/current/functions-string.html

string_to_array ( string text, delimiter text [, null_string text ] ) → text[]

Splits the string at occurrences of delimiter and forms the resulting fields into a text array. If delimiter is NULL, each character in the string will become a separate element in the array. If delimiter is an empty string, then the string is treated as a single field. If null_string is supplied and is not NULL, fields matching that string are replaced by NULL. See also array_to_string.

string_to_array('xx~~yy~~zz', '~~', 'yy') → {xx,NULL,zz}

This breaks the string down into it's component parts and makes an array out of them. The [3:] selects the third through the end of the array elements from the array.

Then from here:

https://www.postgresql.org/docs/current/functions-array.html

array_to_string ( array anyarray, delimiter text [, null_string text ] ) → text

Converts each array element to its text representation, and concatenates those separated by the delimiter string. If null_string is given and is not NULL, then NULL array entries are represented by that string; otherwise, they are omitted. See also string_to_array.

array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '') → 1,2,3,,5

This reconstitutes the string by concatenating the array elements with the delimiter.