Sql and php maybe?

Question

How do you query a specific value from an Sql column containing multiple values, separated by comma?

Example Let the table below represent a sql database with two columns named student and results. Results column contain two comma separated values. How can I select the "percentage" in an an Sql query?

Student   | Results
Student 1 | "marks": 12, "percentage"=2
Student 2 | "marks": 32, "percentage"=5
Student 3 | "marks": 52, "percentage"=9

If you can handle a data set for a medical students website like the above, please dm.

Answer 1

If you don't model a structured world in a structured way, then you might as well use datadog, splunk, or some other tool that doesn't require much structure.

Things that are numerous and have similar structure belong in a database. Chris Date would say a relational database. I agree with him. And since most of my experience is with the Oracle RDBMS, my examples will work for that RDBMS.

You seem to have a table called STUDENT_GRADE with three columns:

1. STUDENT_ID number not null
2. MARKS number not null
3. GRADE not null (percentage is a terrible, terrible name)

The GRADE column is not desirable if it can be derived from MARKS. Your example data, though, does not appear to allow such derivation. So you may have to store GRADE.

---

If you do not normalize the data, then you have to rely on the application conforming to some standard so that you can scan formatted text in a column.

select regexp_replace(RESULTS, '.*"percentage" *= *([[:digit:]]+)', '\1')
  from STUDENT_GRADE
 where STUDENT = 'Student 1'

If someone decides they want to add or remove information or labels with the results column, then you are much more likely to have to modify more code that uses this table. And proving correctness is more difficult.

---

If you do normalize the data, then you can write simpler SQL statements to CRUD the data:

select GRADE
  from STUDENT_GRADE
 where STUDENT_ID = 1

Normalizing the data within a good data model means you have less work to do when changes are introduced and it's easier to prove that the code adheres to a new spec.