0

There is a filed in one of my DB tables that is a text field and contains multiple values (words). How do I go about returning only one of the words contained in the field. The field (state) looks like below, I would like to return the state name and state abbr separately to display is separate html table columns.

---
:name: California
:abbr: CA
MrNiceGuy
  • 13
  • 10
  • 4
    Normalize the schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad). – sticky bit Jan 06 '21 at 17:28
  • 3
    Looks like something is serializing data to YAML. Can you change the schema to at least use a `jsonb` column instead of `text`? – mu is too short Jan 06 '21 at 17:34
  • @muistooshort I made the adjustment to use JSONField() in my model, however I get the following error: ```DETAIL: Token "-" is invalid. CONTEXT: JSON data, line 1: -...``` I believe that I can remove this first line, but am looking for a way to avoid that. – MrNiceGuy Jan 06 '21 at 18:32
  • 1
    You'd have to convert the data from YAML to JSON outside the database before changing the column's type. A fair bit of work but the result will be a lot easier than trying to work with YAML. – mu is too short Jan 06 '21 at 18:51

1 Answers1

0

After google-ing for a while I found and applied the split_part(string text, delimiter text, field int) function.

https://www.postgresql.org/docs/current/functions-string.html

split_part(colName, ':', 3)
split_part(colName, ':', 5)

This will return "California" and "CA" separately.

MrNiceGuy
  • 13
  • 10