Count Delimited value in Column

Question

Please suggest me an SQL Query for the Below I have Ids of Items Stored in one column like this 1,2,3,4 as Delimited or CSV value.
Now i want count no of items in one particular row in one particular cell.

i.e If i have 25,45,26,45,46 in a cell in table.
I want the output like 5 which is nothing but the number of items in a row in a cell say (0,0). Thank you so much for your Replies

Duplicate: http://stackoverflow.com/questions/7020001/how-to-count-items-in-comma-seperated-list-mysql — Ike Walker, May 24 '12 at 14:01
Possible duplicate of [How to count items in comma separated list MySQL](https://stackoverflow.com/questions/7020001/how-to-count-items-in-comma-separated-list-mysql) — LWC, May 21 '18 at 20:00

score 4 · Answer 1 · answered May 24 '12 at 13:19

4

Assuming you don't have values like '123,123,' (note the comma at the end) this should work:

SELECT
LENGTH(yourColumn) - LENGTH(REPLACE(yourColumn, ',', '')) + 1 AS numberOfItemsInRow
FROM yourTable;

Find more information here.

But it would really be better to normalize your database!

answered May 24 '12 at 13:19

fancyPants

50,732
33
89
96

Thank you so much Stack Overflow Rocks – user1093513 May 25 '12 at 03:55
1

@user1093513 I'd appreciate it if you would thank me by accepting my answer (click the check mark to the left of my answer). Thanks. – fancyPants May 25 '12 at 07:46
@we.mamat +1 for +1 :) Did you mean the answer or the comment with being cheeky? The zero to the left of my answer doesn't reflect that. ;) – fancyPants Nov 12 '12 at 09:00
very well, +1 on the anwser as well! :D – we.mamat Nov 13 '12 at 00:07
[hive query] REPLACE -> REGEXP_REPLACE! – seunggabi Jan 17 '19 at 07:52

Count Delimited value in Column

1 Answers1

Linked