CODEWITHSUNDEEP
sqlpostgresqlselect
Hermes
Hermes

Reputation: 462

Postgresql how to get a specified text from text data

I created a table has a column store text data. I want to get specified text value from the column. I want to get PAYMENTDATE value. How can I do? 

"{"SALEDATE":"2017-11-01 12:46:29","PAYMENTDATE":"2017-11-01 12:50:49"}"

Upvotes: 1

Views: 67

Answers (1)

Mureinik
Mureinik

Reputation: 311163

If you trim the leading and trailing " from the string, you'll have a json-formatted string. You could then just cast it to json and use the ->> operator to extract data from it:

SELECT TRIM(BOTH '"' FROM mycol)::JSON->>'PAYMENTDATE'
FROM   mytable

Upvotes: 1

Related Questions