0

How can I get the number of values specified in a MySQL SET column?

list of values shown separated with commas

Is there any built function in mysql ?

Mitch
  • 21,223
  • 6
  • 63
  • 86
underscore
  • 6,495
  • 6
  • 39
  • 78
  • `SELECT COUNT(*) FROM MyTable WHERE SomeCondition` ? –  Jan 12 '14 at 06:37
  • 1
    I have no glue what that picture shows. What do you mean with "SET"? – Markus Malkusch Jan 12 '14 at 06:39
  • I think he means the number of comma-separated elements in the string. – Barmar Jan 12 '14 at 06:40
  • @Barmar yes extracly.Sorry for the bad english.if you can please edit the question ? – underscore Jan 12 '14 at 06:41
  • Except it's a `SET` datatype, not `CHAR` or `VARCHAR`. – Barmar Jan 12 '14 at 06:41
  • @Barmar However how to get the count of it? – underscore Jan 12 '14 at 06:43
  • it's a text datatype. – ajacian81 Jan 12 '14 at 06:46
  • @ajacian81 Why do you say that? The question says it's a SET. SET is displayed as a comma-separated list, but it's stored internally as bits. – Barmar Jan 12 '14 at 06:47
  • @samitha: Your question asks for guessing around. It's not actually clear what the image technically represents. We do not see a table defintion here. There is no SQL query related to the image etc.. And then I've found this already in the Mysql manual: *"To determine all possible values for a `SET` column, use `SHOW COLUMNS FROM tbl_name LIKE set_col` and parse the `SET` definition in the `Type` column of the output."* - https://dev.mysql.com/doc/refman/5.5/en/set.html - it should be part of a well written answer if you're actually asking about `SET`. – hakre Jan 12 '14 at 07:23
  • possible duplicate of [How to count items in comma seperated list MySQL](http://stackoverflow.com/questions/7020001/how-to-count-items-in-comma-seperated-list-mysql) – hakre Jan 12 '14 at 07:34
  • @Barmar it's a set in that it's a comma seperated list, but if you look at the picture, it's defined as a `text` field. – ajacian81 Jan 12 '14 at 14:44
  • @ajacian81 Where do you see the table schema in the picture? – Barmar Jan 13 '14 at 03:09

2 Answers2

2
SELECT id, LENGTH(colname) - LENGTH(REPLACE(colname, ',', '')) + 1 AS set_count
FROM YourTable
Barmar
  • 741,623
  • 53
  • 500
  • 612
0

This answer:

https://stackoverflow.com/a/10738323/1176436

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!

Community
  • 1
  • 1
ajacian81
  • 7,419
  • 9
  • 51
  • 64
  • Why do you think currently it's not normalized? It would be especially good if you point what particular 3NF requirement is violated. Thank you. – zerkms Jan 12 '14 at 06:54
  • I copied and pasted from the answer provided, however, you have a column with multiple values - you can join another table instead with a many to one relationship. – ajacian81 Jan 12 '14 at 07:24
  • are you sure you understand the aim of a `SET` type? – zerkms Jan 12 '14 at 07:24
  • @zerkms it's not a set, it's a text field with a bunch of comma separated values. – ajacian81 Jan 12 '14 at 14:45
  • it's weird because an OP stated it is a `SET` and even provided a link to the corresponding type in documentation – zerkms Jan 12 '14 at 19:57