CODEWITHSUNDEEP
stringpostgresqlsubstring
user2210516
user2210516

Reputation: 683

Get part of string in Postgres

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.

Upvotes: 0

Views: 107

Answers (1)

PrasadU
PrasadU

Reputation: 2438

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

Upvotes: 1

Related Questions