Doubt to count records in a Mysql database query

Asked

Viewed 71 times

-2

In a Mysql database, I have two varchar columns that bring me records in this format:

["165","184","192","209","242"]

One more example:

inserir a descrição da imagem aqui

Each number in double quotes is a record, IE, is the ID of some student.

I need a select that brings me how many Ids there are per column.

In the case of the column curso1, the result would be 8.

It is possible to carry out this type of consultation?

mysql select operator-like

  • takes a balcony in that question, I believe it’s a similar problem

  • Well I see a problem here, one you’re not doing storage more efficiently, so I figured you’d need an N:N ratio table that would make your life easier to get data.

  • What version of Mysql vc is using?

  • @Alvaroalves, Server Version: 10.1.26-Mariadb

1 answer

0


I found a solution to your problem:

I made a test database to which I added the records and made a test, below follows what you need:

SELECT
  curso1,
  CASE WHEN COALESCE(curso1, '"')='"' THEN 0
  ELSE
    length(curso1)-length(replace(curso1, ',', ''))+
    (length(curso1)-length(replace(curso1, ' and ', ''))) DIV 5
    +1
  END
FROM
  suaTabela

change the "suaTable" by the table name and make the query, I tried to perform this procedure via REGEX but it only returns 1 or 0.

Follows illustrative image: inserir a descrição da imagem aqui

  • uuaaull, awesome. Excellent solution. It made me advance a lot. But in the case of your example, for what I need, it would have to return 11. I’m trying with the SUM operator using the query you made.

Browser other questions tagged mysql select operator-like

You are not signed in. Login or sign up in order to post.