Query Help

I have a column with defined datatype of varchar(8000) with the following value in the table.

Table: student

ID studinfo
--- `` ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10 {"students":[{"Subjects_1":["science","Social"],"name":"??","subject_2":["Maths","language arts","social and club"],"subjects_3":["physical education","health","robotics and activities"],"studentName":"SamprasMat"}]}

Expected output:

ID studinfo

10 {"students":[{"Subjects_info":["All subjects"],"name":"??",studentName":"SamprasMat"}]}

Rule: If The combined number of subjects of Subjects_1,special_2,subjects_3 with 4 or more than i need the above output.


In the above case the total of 8 subjects for a student.

Subjects_1:["science","Social"] Total = 2 subjects

subject_2:["Maths","language arts","social and club"] Total = 3 subjects

subjects_3:["physical education","health","robotics and activities"] Total = 3 subjects

Thanks for your help in advance.indent preformatted text by 4 spaces

Look at a splitter function

This looks like JSON.

For pre-SQL2016 versions of SQL Server, look at: