Remove characters from a string sql

Question

I have a table containing some columns like this :

-------------------------------
id      |         Tags
-------------------------------
1       | ['tag1','tag2']
2       | []
3       | ['tag1','tag3']

First i'd like to remove the '[' and ']' characters then I could do something like this :

-------------------------------
id      |         Tags
-------------------------------
1       | 'tag1'
1       | 'tag2'
3       | 'tag1'
3       | 'tag3'

The rows come from an API that I can't modify yet, and I want to do a table that would join the tags and this item id. It's not even a postgresql array so I can't extract the data within a loop, I need to parse this "array" and do a left join ? on the table to get this result. But I don't know how to extract the data properly

Code to generate the table :

CREATE TABLE sample (
"id" bigint,
"tags" text);

INSERT INTO "public"."sample"("id", "tags") VALUES(1,   '[''tag1'',''tag2'']') RETURNING "id", "tags";
INSERT INTO "public"."sample"("id", "tags") VALUES(2, '[]') RETURNING "id", "tags";
INSERT INTO "public"."sample"("id", "tags") VALUES(3, '[''tag1'',''tag3'']') RETURNING "id", "tags";

Thanks in advance

Please edit your question and add the create table statement for the table in question. — user330315
– user330315, Commented Sep 20, 2016 at 15:23

mike.k · Accepted Answer · 2016-09-20 15:57:08Z

1

It would be better to define your column as text[] instead of text so that it holds an array.

The workaround using the current column definition is to cast it to an array and then use unnest()

SELECT
  id,
  unnest(replace(replace(tags, '[', '{'), ']', '}')::text[])
FROM
  sample

gives:

 id | unnest
----+--------
  1 | 'tag1'
  1 | 'tag2'
  3 | 'tag1'
  3 | 'tag3'
(4 rows)

answered Sep 20, 2016 at 15:57

mike.k

3,4771 gold badge14 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Remove characters from a string sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related