Question 404229 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres – Extract Text After Nth Occurance

I have several strings like this:

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

Basically I need to return everything after the second colon :. So, from the above string I really just need:

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

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

Many thanks!

Tags:

2

Answers


  1. 0 
    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.

    Login or Signup to reply.
  2. 0

    This one returns an array, by splitting the content on the :

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

    If you need a single string:

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

    This one uses a regex to split the content and also returns an array, but just one element and that’s easy to convert to a single string:

    SELECT (regexp_matches('1:723:NVDA:NSDQ::Z4189274321:Snapshot Report:10:COMPUTER & TECHNOLOGY:241:Semiconductor', '^[^:]*:[^:]*:(.*)', 'g'))[1];
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search