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

SQL Query (PostgreSQL)

Please advise how to achieve below ask in PostgreSQL query

Input

Column_x
null
null
1257-1;1258-3;1235-4;111-1
1260-3
1263-6;1457-1
null
null

Basically, need to count of id’s

Expected output

Column_x 
0
0
4 (Count of Id's)
1
2
0
0

Since I’m new to SQL, did not find much help on internet

Tags:

2

Answers


  1. 0

    This can be done using regexp_split_to_table to split a string into a table, then use group by and count to count the ids :

    select t.column_x, coalesce(_count, 0)
from mytable t
left join (
  select column_x, count(*) as _count
  from mytable
  cross join lateral regexp_split_to_table(Column_x, ';') AS t(x)
  group by column_x
) as s on s.column_x = t.column_x

    Demo here

    Login or Signup to reply.
  2. 0

    I take it the column type is text/varchar? If so this code should do it:

    SELECT
    Column_x,
    COALESCE(ARRAY_LENGTH(STRING_TO_ARRAY(Column_x, ';'), 1), 0) AS id_count
FROM table_name;

    And some explanation – reading the parenthesis from inner-most:

    • STRING_TO_ARRAY(Column_x, ';') takes the text field and turns it into array, using ";" as a delimiter.
    • ARRAY_LENGTH(array, 1) returns the number of elements in the array.
    • COALESCE(int, 0) is here, because the null rows will return a count of null, so we use coalesce to make them "0"
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search