Question 192086 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to count elements inside a text field

In my PostgreSQL DB, I have a table with a field of the type text which contains a string like this:

|ABX1-001|ABX1-002|ABX1-004|ABX1-005|

I would like to count the items inside the text field current_ids.
This is the structure of the table:

CREATE TABLE "public"."candidates" (
    "day" timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "study_id" varchar(6) NOT NULL,
    "site_id" varchar(32),
    "status" varchar(32) NOT NULL,
    "total" int4 NOT NULL,
    "current" int4 NOT NULL,
    "referrer_token" varchar(255) NOT NULL DEFAULT 'NO_REFERRER_TOKEN'::character varying,
    "total_ids" text NOT NULL DEFAULT '|'::text,
    "current_ids" text NOT NULL DEFAULT '|'::text
);

Some sample data:

2020-01-01 00:00:00+00 ABX1 USA-1 INCOMPLETE 4 4 NO_REFERRER_TOKEN |ABX1-001|ABX1-002|ABX1-004|ABX1-004|
2020-01-01 00:00:00+00 ABX1 USA-1 INCOMPLETE 9 7 NO_REFERRER_TOKEN |ABX1-009|ABX1-010|ABX1-011|ABX1-012|ABX1-013|ABX1-014|ABX1-016|ABX1-018|ABX1-020|
Tags:

2

Answers


  1. 0

    Using functions from here String functions and function from here Array functions:

    select array_length(string_to_array(trim('|ABX1-001|ABX1-002|ABX1-004|ABX1-005|', '|'), '|'), 1);

 array_length 
--------------
            4

    The trim function removes the leading and trailing | and then string_to_array splits the string on the remaining | to produce a single dimension text array. The array_length function then counts the number of elements in the 1 array dimension.

    Alternate:

    select count(*) from  string_to_table(trim('|ABX1-001|ABX1-002|ABX1-004|ABX1-005|', '|'), '|');

    The trim does the same thing and then string_to table produces a table with each element in a row which then counted using count.

    Login or Signup to reply.
  2. 0

    That all depends.
    If we can rely on a fixed length of 8 characters per key and 1 character per separator, then integer division is a very simple & fast way:

    SELECT *, length(total_ids) / 9 AS current_ids_count
FROM   candidates;

    Integer division truncates, so leading and dangling noise doesn’t matter as long as it stays below 9 characters total.

    If the length can vary, but we can rely on the separator | (and no leading or dangling white space), then consider Adrian‘s solutions.

    Typically, it’s best to store such data as actual array, or in normalized form as separate table with n:1 relationship to candidates to begin with.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search