Get part of string in Postgres

Question

Can someone help what i need to write to find the and print the ID in this string.

with t(txt) as
(
 values
  ('[["+","created_at","20210713T08:30:16.590+02:00"],["+","default_zahlungsverbindung_id",null],["+","firmen_gruppe_id",null],["+","gueltig_bis",null],["+","hat_mahnsperre",false],["+","hat_zahlsperre",false],["+","heimatort",null],["+","id",188710],["+","ist_aktiv",true]]')
)
select 
    substring (txt from ????) as id
from t;

I only want the get 188715 in return. The lenght of the string can change but this part is always the same "id",188710]. So i can say i want everything after "id", until ] but i don't know how.

PrasadU · Accepted Answer · 2021-09-28 11:18:52Z

1

with t(txt) as
(
 values
  ('[["+","created_at","20210713T08:30:16.590+02:00"],["+","default_zahlungsverbindung_id",null],["+","firmen_gruppe_id",null],["+","gueltig_bis",null],["+","hat_mahnsperre",false],["+","hat_zahlsperre",false],["+","heimatort",null],["+","id",188710],["+","ist_aktiv",true]]')
)
select regexp_replace(txt, '.*"id"\s*,\s*(\d*)\s*.*]','\1') as id from t;```

ps - it looks like proper json - if its already json in DB, use proper json functions https://www.postgresql.org/docs/current/functions-json.html

answered Sep 28, 2021 at 11:18

PrasadU

2,4361 gold badge12 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2210516 Over a year ago

Yes it's json. So your example is not the best way?

Collectives™ on Stack Overflow

Get part of string in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related