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

Postgresql – Ignore after n-th delimiter of a split_part

I have a text like this:

'a:b:c:d'

I would like to ignore the last part of the split text, my result should be

'a:b:c'

I tried split_part on it, but it doesn’t work, neither applying reverse at it.

Queries I tried and didn’t worked:

select split_part('a:b:c:d', ':', 1);

select reverse(split_part(reverse('a:b:c:d'), ':', -1));
Tags:

5

Answers


  1. 0

    You can use substring and find the last : in the string

    SELECT substring('a:b:c:d' from 1 for length('a:b:c:d') - position(':' in reverse('a:b:c:d')) + 1 )
    substring
    a:b:c:

    fiddle

    Login or Signup to reply.
  2. 0

    Use a substring() with a regular expression: demo

    select substring('a:b:c:d' FROM '^(([^:]*:){2}[^:]*)');--first 3 groups
select substring('a:b:c:d' FROM '^(.*):[^:]*');--all but the last group

    Which is looking for two sets of non-colon characters that end with a colon, then then one such set after that. It uses POSIX syntax

    • ^ at the beginning, outside square brackets, means you start at the beginning
    • [^:] means any character except a colon
    • . means any character, at all
    • * means any amount of the previous thing
    • [^:]* means any amount of that anything, except a colon
    • [^:]*: means the same, followed by an actual colon
    • ([^:]*:){2} means the same, just repeated twice

    Since PostgreSQL arrays support subscripts and slices, you can also split the text into an array, use a [:3] to get your first 3 elements, then reassemble the text:

    --first 3 groups
select array_to_string((string_to_array('a:b:c:d',':'))[:3],':');
--all but the last group
select reverse(array_to_string((string_to_array(reverse('a:b:c:d'),':'))[2:],':'));

    You can also do it your own way:

    select reverse( substring( reverse('a:b:c:d') 
                           from 
                           1+position(':' in reverse('a:b:c:d') )
                          )
               );--all but the last group

    This spots the position of the first colon in reversed version of the text, saves only what comes after that first colon, then reverses it back again.

    Login or Signup to reply.
  3. 0

    With only one example, your goal is ambiguous. The following query demonstrates approaches using regular expressions to keep only the portion with the first n fields, or all but the last field:

    WITH t(test_string) AS (
  VALUES ('a:b:c:d'), ('a:b:c:d:e'))
SELECT t.test_string,
       s.n,
       regexp_replace(t.test_string, FORMAT('^((([^:]*):){%s}[^:]*).*$', s.n - 1),'1') AS first_n,
       regexp_replace(t.test_string, '^(.*):[^:]*$', '1') AS exclude_last
  FROM t
    CROSS JOIN generate_series(1, 4) s(n)
  ORDER BY t.test_string, s.n;

    Running this query produces the following output:

    test_string n first_n exclude_last
    a:b:c:d 1 a a:b:c
    a:b:c:d 2 a:b a:b:c
    a:b:c:d 3 a:b:c a:b:c
    a:b:c:d 4 a:b:c:d a:b:c
    a:b:c:d:e 1 a a:b:c:d
    a:b:c:d:e 2 a:b a:b:c:d
    a:b:c:d:e 3 a:b:c a:b:c:d
    a:b:c:d:e 4 a:b:c:d a:b:c:d
    Login or Signup to reply.
  4. 0

    You can use the SQL query below to ignore the final character of a text string in PostgreSQL, such as changing ‘a:b:c:d’ into ‘a:b:c’. This command basically removes the last section of the text by extracting a substring from the original string .

    SELECT substring('a:b:c:d' FROM 1 FOR strpos('a:b:c:d', ':' FROM length('a:b:c:d') - 1));
    Login or Signup to reply.
  5. 0

    If you indeed want to "ignore after n-th character", like the title says, just use left():

    SELECT left('a:b:c:d', 5);  -- → 'a:b:c'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search