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

substring in postgresql with additional criteria based on the position of a character

I am hitting a SQL query below and resulting as show below.
SELECT column from Table LIMIT =1

GLOBE-WORLD-AIRPORT-Azerbaijan-country-SECRET-ABORT

Here I want to get the data Azerbaijan-country as my result. My logic should fetch the content after the third – and the second – from the last. So that I can get the required result. How to do this in PostgreSQL.

Tags:

2

Answers


  1. 0

    You can use regexp_substr() in PostgreSQL 15+: demo at db<>fiddle

    select regexp_substr("column",'([^-]*-){3}(.*)(-[^-]*){2}', 1, 1, 'i', 2) 
from "Table"
limit 1;
    regexp_substr
    Azerbaijan-country

    You can read more on pattern matching in PostgreSQL in the doc.

    1. [^-] means any character except a hyphen.
    2. ([^-]*-) means any amount of non-hyphens followed by a hyphen.
    3. ([^-]*-){3} means 3 repetitions of that. This is looking for your initial three hyphens and anything they separate.
    4. (.*) is any amount of any character. This is what you want to catch
    5. (-[^-]*) is a hyphen followed by any amount of non-hyphens.
    6. (-[^-]*){2} looks for two of those after your target.
    7. The parameters after the pattern 1, 1, 'i', 2 mean (in this order) that you want to
      • start counting from the 1st character of your text value
      • get only 1 hit
      • search case-insensitively,
      • get the hit in the 2nd parenthesised group – the (.*).

    In earlier versions all the way back to 9.4, you can use regexp_replace() to wipe everything around your target: demo

    select regexp_replace(
            regexp_replace( "column"
                           ,'^([^-]*-){3}'
                           ,'')
           ,'(-[^-]*){2}$'
           ,'')
from "Table"
limit 1;

    This uses ^ outside [] as beginning of line idicator and $ as end of line indicator.

    Login or Signup to reply.
  2. 0

    There Some doubts about used RDBMS.
    I will offer a solution without using regexp.

    select id,words
  ,string_agg(w,'-' order by rn) newWords
from(
    select *
      ,row_number()over(partition by id order by (select null)) rn
      ,count(*)over(partition by id order by (select null)) cnt
    from(select *,string_to_table(words,'-') w from test )t
  )t2
where rn>3 and rn<=(cnt-2)
group by id,words

    Output is

    id words newwords
    1 GLOBE-WORLD-AIRPORT-Azerbaijan-country-SECRET-ABORT Azerbaijan-country
    2 GLOBE-WORLD-AIRPORT-Azerbaijan-country-SECRET Azerbaijan

    from test data

    id words
    1 GLOBE-WORLD-AIRPORT-Azerbaijan-country-SECRET-ABORT
    2 GLOBE-WORLD-AIRPORT-Azerbaijan-country-SECRET
    3 GLOBE-WORLD-AIRPORT-Azerbaijan-country
    4 GLOBE-WORLD-AIRPORT-Azerbaijan
    5 GLOBE-WORLD-AIRPORT
    6 GLOBE-WORLD

    string_to_table actual from postgresql 14.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search