Explode and count in SQL

Question

I have column in table views: 165,75,44,458,458,42,45 This column contain user_ids who viewed a link. I want to explode it by comma delimiter and count the ids

I tried this but it only counts all the character.

SQL:

SELECT LENGTH(views) FROM questions WHERE question_id=1;

This is the PHP version that I want to do in SQL

$viewers_ids  = "165,75,44,458,458,42,45";
$num_of_views = count(array_filter(explode(",", $viewers_ids)));

score 6 · Accepted Answer · answered Feb 26 '19 at 21:12

6

You can count the commas:

select 1 + length(views) - length(replace(views, ',', ''))

That said, you should fix your data structure. You should not be storing multiple numeric ids in a string column -- that is just wrong in many ways. You should be using a junction/association table.

answered Feb 26 '19 at 21:12

Gordon Linoff

1,242,037
58
646
786

Little corner case if your string is null, this solution will give you 1 instead of 0 ... I think :) – LauDec Feb 26 '19 at 21:17
@LauDec . . . If the string is `NULL`, the value will be `NULL`. The edge case is an empty string. I'm not sure if that is an issue, but if it is, the `1` could be replaced with `(views is not null)`. – Gordon Linoff Feb 26 '19 at 21:35
Yes sorry wanted to say empty string :) well as long as OP makes sure that he thought about this, we're good :) – LauDec Feb 26 '19 at 21:46

Himanshu · Answer 2 · 2019-02-26T21:51:34.090

0

Tried the other way around i.e. to replace all characters other than ,

     Select  
      Length(REGEXP_REPLACE
      (views,'[^\,]*',''))+1 from
         table

edited Feb 26 '19 at 21:51

answered Feb 26 '19 at 21:27

Himanshu

3,830
2
10
29

hummmm ...SELECT LOCATE('165,75,44,458,458,42,45',',',-1) +1 returns 1 on sqlfiddle. so i don't think it works – LauDec Feb 26 '19 at 21:32
ohkay got it mysql din work as i thought coz its got complex syntax. ill update in a while – Himanshu Feb 26 '19 at 21:36

score 0 · Answer 3 · answered Feb 26 '19 at 22:02

My immediate reaction is that your table structure needs to change - I would expect there to be another table that joins question links and user_ids with a "visit_id" perhaps, and use visit_id as a foreign key in the views table.

My second reaction is that this is a perfect problem for a shell utility - maybe sed/,/ /g then wc -w, or a small awk script.

To do it in SQL, I might try to figure out how to count the number of commas, and add one. This answer gives you a function that will get you the number of commas by replacing them with "", then substracting the length from the length of the unmodified field: Count the number of occurrences of a string in a VARCHAR field?

Explode and count in SQL

3 Answers3