CODEWITHSUNDEEP
postgresql
TLR
TLR

Reputation: 587

Remove characters from a string sql

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

Upvotes: 0

Views: 1931

Answers (1)

mike.k
mike.k

Reputation: 3457

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)

Upvotes: 1

Related Questions