Postgresql how to get a specified text from text data

Question

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"}"

why dont use a JSON column instead? postgresql.org/docs/9.4/static/datatype-json.html — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Dec 1, 2017 at 14:40
You are right. I know JSON type. But I have to use text data. — Hermes
– Hermes, Commented Dec 1, 2017 at 14:42
You still can cast the text field as JSON and work with it. text_data::JSON — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Dec 1, 2017 at 14:43
I solved such as thanks. columnName::json->>'PAYMENTDATE' as paymentDate — Hermes
– Hermes, Commented Dec 1, 2017 at 14:43

Mureinik · Accepted Answer · 2017-12-01 14:46:12Z

1

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

edited Dec 1, 2017 at 14:46

answered Dec 1, 2017 at 14:44

Mureinik

316k54 gold badges396 silver badges405 bronze badges

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

3 Comments

Juan Carlos Oropeza Over a year ago

My guess those " are the result from printing the field content.

Hermes Over a year ago

columnName::json->>'PAYMENTDATE' as paymentDate. This is worked thanks.

Mureinik Over a year ago

@JuanCarlosOropeza so it's even easier then - just lose the trim call

Collectives™ on Stack Overflow

Postgresql how to get a specified text from text data

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related